0
# Query Interface
1

2
Enhanced query classes with Flask-specific convenience methods for common web patterns like 404 error handling and result processing.
3

4
## Capabilities
5

6
### Query Class
7

8
Enhanced SQLAlchemy Query with Flask convenience methods for web applications.
9

10
```python { .api }
11
class Query(sa_orm.Query):
12
    """
13
    SQLAlchemy Query subclass with Flask-specific convenience methods.
14
    
15
    Provides common web application patterns like automatic 404 error
16
    handling for missing results. This is the default query class for
17
    Model.query properties.
18
    
19
    Note: The query interface is considered legacy in SQLAlchemy.
20
    Modern code should prefer session.execute(select()) patterns.
21
    """
22
    
23
    def get_or_404(self, ident: Any, description: str | None = None) -> Any:
24
        """
25
        Get record by primary key or abort with 404 Not Found.
26
        
27
        Parameters:
28
        - ident: Primary key value to lookup
29
        - description: Custom error message for 404 page
30
        
31
        Returns:
32
        Model instance matching the primary key
33
        
34
        Raises:
35
        404 error if no record found with given primary key
36
        """
37
    
38
    def first_or_404(self, description: str | None = None) -> Any:
39
        """
40
        Get first result from query or abort with 404 Not Found.
41
        
42
        Parameters:
43
        - description: Custom error message for 404 page
44
        
45
        Returns:
46
        First model instance from query results
47
        
48
        Raises:
49
        404 error if query returns no results
50
        """
51
    
52
    def one_or_404(self, description: str | None = None) -> Any:
53
        """
54
        Get exactly one result from query or abort with 404 Not Found.
55
        
56
        Parameters:
57
        - description: Custom error message for 404 page
58
        
59
        Returns:
60
        Single model instance from query results  
61
        
62
        Raises:
63
        404 error if query returns no results or multiple results
64
        """
65
    
66
    def paginate(
67
        self,
68
        *,
69
        page: int | None = None,
70
        per_page: int | None = None,
71
        max_per_page: int | None = None,
72
        error_out: bool = True,
73
        count: bool = True,
74
    ) -> Pagination:
75
        """
76
        Apply pagination to the query results.
77
        
78
        Parameters:
79
        - page: Current page number (from request args if None)
80
        - per_page: Items per page (from request args if None) 
81
        - max_per_page: Maximum allowed per_page value
82
        - error_out: Abort with 404 on invalid page parameters
83
        - count: Calculate total item count for pagination metadata
84
        
85
        Returns:
86
        Pagination object containing page results and metadata
87
        """
88
```
89

90
### Query Property
91

92
The `_QueryProperty` class that provides the `query` attribute on model classes.
93

94
```python { .api }
95
class _QueryProperty:
96
    """
97
    Class property that creates query objects for model classes.
98
    
99
    Internal implementation detail that provides Model.query property.
100
    """
101
    
102
    def __get__(self, obj: Model | None, cls: type[Model]) -> Query:
103
        """
104
        Return configured query instance for the model class.
105
        
106
        Parameters:
107
        - obj: Model instance (unused)
108
        - cls: Model class to create query for
109
        
110
        Returns:
111
        Query instance configured for the model class
112
        """
113
```
114

115
## Usage Examples
116

117
### Basic Query Operations
118

119
```python
120
# Model with automatic query property  
121
class User(db.Model):
122
    id = db.Column(db.Integer, primary_key=True)
123
    username = db.Column(db.String(80), unique=True)
124
    email = db.Column(db.String(120))
125

126
# Basic querying
127
users = User.query.all()
128
user = User.query.get(1)
129
active_users = User.query.filter_by(active=True).all()
130
```
131

132
### Flask Error Handling  
133

134
```python
135
@app.route('/user/<int:user_id>')
136
def show_user(user_id):
137
    # Automatically returns 404 if user doesn't exist
138
    user = User.query.get_or_404(user_id)
139
    return f"User: {user.username}"
140

141
@app.route('/user/first-admin')
142
def first_admin():
143
    # Get first admin user or 404 if none exist
144
    admin = User.query.filter_by(role='admin').first_or_404()
145
    return f"First admin: {admin.username}"
146

147
@app.route('/user/email/<email>')
148
def user_by_email(email):
149
    # Expect exactly one user with this email or 404
150
    user = User.query.filter_by(email=email).one_or_404()
151
    return f"User: {user.username}"
152
```
153

154
### Custom Error Messages
155

156
```python
157
@app.route('/user/<int:user_id>')
158
def show_user(user_id):
159
    user = User.query.get_or_404(
160
        user_id, 
161
        description=f"User {user_id} not found in our system"
162
    )
163
    return f"User: {user.username}"
164
```
165

166
### Query Pagination
167

168
```python
169
@app.route('/users')
170
def list_users():
171
    page = request.args.get('page', 1, type=int)
172
    
173
    # Paginate query results
174
    pagination = User.query.order_by(User.username).paginate(
175
        page=page,
176
        per_page=20,
177
        max_per_page=100,
178
        error_out=False  # Don't 404 on invalid page, use page 1
179
    )
180
    
181
    return {
182
        'users': [u.username for u in pagination.items],
183
        'page': pagination.page,
184
        'pages': pagination.pages,
185
        'has_next': pagination.has_next,
186
        'has_prev': pagination.has_prev
187
    }
188
```
189

190
### Complex Queries
191

192
```python
193
# Filtering and ordering
194
recent_posts = Post.query.filter(
195
    Post.created_at > datetime.utcnow() - timedelta(days=7)
196
).order_by(Post.created_at.desc()).all()
197

198
# Joins and relationships  
199
posts_with_authors = Post.query.join(User).filter(
200
    User.active == True
201
).order_by(Post.title).all()
202

203
# Aggregation
204
user_post_counts = db.session.query(
205
    User.username,
206
    db.func.count(Post.id).label('post_count')
207
).join(Post).group_by(User.id).all()
208
```
209

210
### Query Customization
211

212
```python
213
# Custom query class
214
class CustomQuery(Query):
215
    def active(self):
216
        return self.filter_by(active=True)
217
    
218
    def by_username(self, username):
219
        return self.filter_by(username=username)
220

221
# Use custom query class
222
db = SQLAlchemy(app, query_class=CustomQuery)
223

224
class User(db.Model):
225
    id = db.Column(db.Integer, primary_key=True)
226
    username = db.Column(db.String(80))
227
    active = db.Column(db.Boolean, default=True)
228

229
# Now all queries have custom methods
230
active_users = User.query.active().all()
231
user = User.query.by_username('john').first_or_404()
232
```
233

234
### Query with Select (Modern Pattern)
235

236
While the Query interface is available, SQLAlchemy 2.x style is preferred:
237

238
```python
239
# Legacy Query style (still supported)
240
users = User.query.filter_by(active=True).all()
241

242
# Modern select() style (recommended)
243
users = db.session.execute(
244
    db.select(User).where(User.active == True)
245
).scalars().all()
246

247
# Both can use Flask conveniences
248
user = db.get_or_404(User, user_id)  # Modern
249
user = User.query.get_or_404(user_id)  # Legacy
250
```
251

252
### Relationship Queries
253

254
```python
255
class User(db.Model):
256
    id = db.Column(db.Integer, primary_key=True)
257
    posts = db.relationship('Post', backref='author', lazy='dynamic')
258

259
class Post(db.Model):
260
    id = db.Column(db.Integer, primary_key=True)
261
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
262

263
# Query through relationships
264
user = User.query.get(1)
265
user_posts = user.posts.filter_by(published=True).all()
266

267
# Backref queries
268
post = Post.query.get(1)
269
post_author = post.author  # Automatically loaded
270
```
271

272
## Performance Considerations
273

274
- **Query Interface Legacy**: The Query interface is considered legacy in SQLAlchemy 2.x
275
- **Prefer Select**: Use `db.session.execute(db.select())` for new code
276
- **Query Caching**: Consider query result caching for frequently accessed data
277
- **Eager Loading**: Use `joinedload()` or `selectinload()` to avoid N+1 queries
278
- **Pagination**: Always paginate large result sets to avoid memory issues