SQLAlchemy ORM - Model.query

The query object is an object that exists on a given model that we have defined in a SQLAlchemy application. This query object is essentially the source of all select statements that would be generated by the ORM, offering you the ability to return filtered slices of the data that comes from our database tables.

ClassName.query.filter_by(name='example')
ClassName.query.all()
ClassName.query.count()
ClassName.query.filter(ClassName.name == 'example')
ClassName.query.filter(ClassName.name == 'example', AnotherClassName.name == 'new_example')
ClassName.query.get(1)  # gets by primary key

# Delete
ClassName.query.filter_by(category = 'Misc').delete()

---

Model.query method chaining

ClassName.query
	.filter(ClassName.name == 'example')
	.filter(Team.name == 'example')
	.first()

ClassName.query
	.join('another_className')
	.filter_by(example_id=3)
	.all()

Method chaining is essential for when we need to do things like joints and joined filtering.

---

There are two ways by which you can access the query object on a given model.

ClassName.query
#Same as
db.session.query(ClassName)

db.session.query(ClassName)

session.query(ClassName).join(AnotherClassName)

ClassName.query
	.filter(ClassName.name == 'example')
	.filter(AnotherClassName.name == 'another_example')
	.first()

# Same as

db.session.query(ClassName)
	.filter(ClassName.name == 'example')
	.filter(AnotherClassName.name == 'another_example')
	.first()

Query Methods

• Select records
  all()

MyModel.query.all()

same as doing a ’ SELECT * ', fetching all records from the model’s table. Returns a list of objects.
first()

MyModel.query.first()

Fetches just the first result. Return either None or an object if found

• Filtering
  filter_by

MyModel.query.filter_by(my_table_attribute='some value')

Similar to doing a SELECT * from … WHERE SQL statement for filtering data by named attributes.
filter

#Example
MyModel.query.filter(MyOtherModel.some_attr='some value')
OrderItem.query.filter(Product.id=3)

Similar to filter_by, but instead, you specify attributes on a given Model. It is more flexible than using filter_by itself, and is especially useful when querying from a joined table where you want to filter by attributes that span across multiple models.

equals:                             query.filter(User.name == 'ed')

not equals:                         query.filter(User.name != 'ed')

LIKE:                               query.filter(User.name.like('%ed%'))

ILIKE (case-insensitive LIKE):      query.filter(User.name.ilike('%ed%'))

IN:                                 query.filter(User.name.in_(['ed', 'wendy', 'jack']))

NOT IN:                             query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

IS NULL:                            query.filter(User.name == None)

IS NOT NULL:                        query.filter(User.name != None)

AND:                                query.filter(User.name == 'ed', User.fullname == 'Ed Jones')       or chain filter methods together

OR:                                 query.filter(User.name == 'a' | User.name == 'b')

MATCH:                              query.filter(User.name.match('wendy'))

common filter operators here

• Ordering
  order_by

MyModel.order_by(MyModel.created_at)
MyModel.order_by(db.desc(MyModel.created_at))

To order the results by a given attribute. Use db.desc to order in descending order.

limit

Order.query.limit(100).all()

limit(max_num_rows) limits the number of returned records from the query. ala LIMIT in SQL.

• Aggregates
  count()

query = Task.query.filter(completed=True)
query.count()

Returns an integer set to the number of records that would have been returned by running the query.

get()
Get object by ID

model_id = 3
MyModel.query.get(model_id)

Returns the object as a result of querying the model by its primary key.

• Bulk Deletes

query = Task.query.filter_by(category='Archived')
query.delete()

delete() does a bulk delete operation that deletes every record matching the given query.

• Joined Queries

Driver.query.join('vehicles')

Query has a method join(<table_name>) for joining one model to another table.

---

In Summary

• db.Model.query offers us the Query object. The Query object lets us generate SELECT statements that let us query and return slices of data from our database.
• Query has method chaining. You can chain one query method to another (indefinitely), getting back more query objects, until you chain it with a terminal method that returns a non-query object like count(), all((), first(), delete(), etc.
• The Query object can be accessed on a model using either:
  • MyModel.query directly on the model, or
  • db.session.query(MyModel) using db.session.query instead.

---