Test complex query patterns
(self)
| 188 | session.close() |
| 189 | |
| 190 | def test_complex_queries(self): |
| 191 | """Test complex query patterns""" |
| 192 | session = self.Session() |
| 193 | try: |
| 194 | # Setup test data |
| 195 | # ages will be: 21, 22, 23, 24, 25 (avg = 23) |
| 196 | users = [ |
| 197 | User(username=f"user{i}", email=f"user{i}@example.com", age=20+i, balance=Decimal(str(100*i))) |
| 198 | for i in range(1, 6) |
| 199 | ] |
| 200 | session.add_all(users) |
| 201 | session.commit() |
| 202 | |
| 203 | # Test AND/OR conditions |
| 204 | result = session.query(User).filter( |
| 205 | or_( |
| 206 | User.age > 22, |
| 207 | and_(User.age == 21, User.username == "user1") |
| 208 | ) |
| 209 | ).all() |
| 210 | assert len(result) == 4 |
| 211 | |
| 212 | # Test NOT condition |
| 213 | result = session.query(User).filter( |
| 214 | not_(User.username.in_(["user1", "user2"])) |
| 215 | ).all() |
| 216 | assert len(result) == 3 |
| 217 | |
| 218 | # Test LIKE pattern |
| 219 | result = session.query(User).filter( |
| 220 | User.email.like("%user%") |
| 221 | ).all() |
| 222 | assert len(result) == 5 |
| 223 | |
| 224 | # Test aggregates |
| 225 | avg_age = session.query(func.avg(User.age)).scalar() |
| 226 | # SQLite returns Decimal for AVG |
| 227 | assert float(avg_age) == 23.0 # (21+22+23+24+25)/5 |
| 228 | |
| 229 | max_balance = session.query(func.max(User.balance)).scalar() |
| 230 | assert max_balance == Decimal('400') |
| 231 | |
| 232 | # Test GROUP BY |
| 233 | result = session.query( |
| 234 | User.age, |
| 235 | func.count(User.id) |
| 236 | ).group_by(User.age).all() |
| 237 | assert len(result) == 5 |
| 238 | |
| 239 | finally: |
| 240 | session.close() |
| 241 | |
| 242 | def test_joins(self): |
| 243 | """Test various join operations""" |