Skip to main content
Version: Next

Querying model records

Once models are properly defined, it is possible to leverage the querying API in order to interact with model records. This API lets you build what is commonly referred to as "query sets": that is, representations of records collections that can be read, filtered, updated, or deleted.

This documents covers the main features of the query set API. Most of the examples used to illustrate these features will refer to the following models:

class City < Marten::Model
  field :id, :big_int, primary_key: true, auto: true
  field :name, :string, max_size: 255
end

class Author < Marten::Model
  field :id, :big_int, primary_key: true, auto: true
  field :first_name, :string, max_size: 255
  field :last_name, :string, max_size: 255
  field :hometown, :foreign_key, to: City, blank: true, null: true
end

class Article < Marten::Model
  field :id, :big_int, primary_key: true, auto: true
  field :title, :string, max_size: 255
  field :content, :text
  field :author, :many_to_one, to: Author
end

Creating new records​

New model records can be created through the use of the #new and #create methods. The #new method will simply initialize a new model record that is not persisted in the database. The #create method will initialize the new model record using the specified attributes and then persist it to the database.

For example, it is possible to create a new Author model record by specifying their first_name and last_name attribute values through the use of the create method like this:

Author.create(first_name: "John", last_name: "Doe")

The same Author record could be initialized (but not saved!) through the use of the new method as follows:

Author.new(first_name: "John", last_name: "Doe")

In the previous example the model instance will not be persisted to the database automatically. In order to explicitly save it to the database it is possible to use the #save method:

author = Author.new(first_name: "John", last_name: "Doe") # not persisted yet!
author.save                                               # the author is now persisted to the database!

Finally, it should be noted that both #create and #new support an optional block that will receive the initialized model record. This allows to initialize attributes or to call additional methods on the record being initialized:

Author.create do |author|
  author.first_name = "John"
  author.last_name = "Doe"
end
caution

Model records will be validated before being saved to the database. If this validation fails, both the #create and #save methods will silently fail: #create will return the invalid model instance while #save will return false. The #create and #save methods also have bang counterparts (#create! and #save!) that will explicitly raise a validation error (Marten::DB::Errors::InvalidRecord) in case of an invalid record.

Please refer to Validations in order to learn more about model validations.

Basic querying capabilities​

In order to interact with a collection of model records, it is necessary to construct a "query set". A query set represents a collection of model records in the database. It can have filters, be paginated, etc. Unless a specific "write" operation is performed on such query sets, they will usually be mapped to a standard SELECT statement where filters are converted to WHERE clauses.

Query sets can be forged from a specific model by using methods such #all, #filter, or #exclude (those are described below). One of the key characteristics of query sets is that they are lazily evaluated: defining a query set will usually not involve any database operation. Additionally, most methods provided by query sets also return new query set objects. Query sets are only translated to SQL queries hitting the underlying database when records need to be extracted or manipulated by the considered codebase.

For example, filters can be chained on a query set without it being evaluated. The query set is only evaluated when the actual records need to be displayed or when it becomes necessary to interact with them:

qset = Article.filter(title__startswith: "Top") # the query set is not evaluated
qset = qset.filter(author__first_name: "John")  # the query set is not evaluated
puts qset                                       # the query set is evaluated

In the above example, the two filters are simply chained without these resulting in database hits. The query set is only evaluated when the actual records need to be printed.

Query sets are iterable: they provide the ability to iterate over the resulting records (which will also force the query set to be evaluated when this happens):

qset = Article.filter(title__startswith: "Top") # the query set is not evaluated
qset.each { |article| puts article }            # the query set is evaluated

Querying all records​

Retrieving all the records of a specific model can be achieved through the use of the #all method:

Author.all

"All records" does not necessarily mean all the records in the considered table. For example, #all can be chained to an existing query set that was filtered (which is usually unnecessary since this does not alter the resulting records):

Author.filter(first_name: "John").all

Filtering specific records​

Filtering records is achieved through the use of the #filter method. The #filter method requires one or many predicate keyword arguments (in the format described in Field predicates). For example:

Author.filter(first_name: "John")

The above query set will return Author records whose first name is "John".

It’s possible to filter records using multiple filters. For example, the following queries are equivalent:

Author.filter(first_name: "John").filter(last_name: "Doe")
Author.filter(first_name: "John", last_name: "Doe")

By default, filters involving multiple parameters like in the above examples always produce SQL queries whose parameters are "AND"ed together. More complex queries (eg. using AND, OR, XOR, or NOT conditions) can be achieved through the use of the q DSL (which is described in Complex filters with q expressions), as outlined by the following examples:

# Get Author records with either "Bob" or "Alice" as first name
Author.filter { q(first_name: "Bob") | q(first_name: "Alice") }

# Get Author records whose first names are not "John"
Author.filter { -q(first_name: "Alice") }

Marten also has the option to filter query sets using raw SQL predicates. This is useful when you want to leverage the flexibility of SQL for specific conditions, but still want Marten to handle the column selection and query building for the rest of the query. To use raw SQL predicates, can specify a string containing the predicate with optional parameters to the #filter query set method:

Author.filter("first_name = :first_name", first_name: "John")
Author.filter("first_name = ?", "John")
Author.filter { q("first_name = :first_name", first_name: "John") }

Excluding specific records​

Excluding records is achieved through the use of the #exclude method. This method provides exactly the same API as the #filter method outlined previously. It requires one or many predicate keyword arguments (in the format described in Field predicates). For example:

Author.exclude(first_name: "John")

Author.exclude(first_name: "John").exclude(last_name: "Doe")
Author.exclude(first_name: "John", last_name: "Doe")

Author.exclude { q(first_name: "Bob") | q(first_name: "Alice") }

Retrieving a specific record​

Retrieving a specific record is achieved through the use of the #get method, which requires one or many predicate keyword arguments (in the format described in Field predicates). For example:

Author.get(id: 1)
Author.get(first_name: "John")

If the record is not found, nil will be returned. It should be noted that a bang version of this method also exists: #get!. This alternative method raises a Marten::DB::Errors::RecordNotFound error if the record is not found. Regardless of the method used, if multiple records are found for the passed predicates, a Marten::DB::Errors::MultipleRecordsFound error is raised.

It is also possible to chain a #get call on a query set that was already filtered:

Author.filter(first_name: "John").get(id: 42)

A record can also be retrieved with a raw SQL predicate. For example:

Author.get("id=?", 42)
Author.get("id=:id", id: 42)

Retrieving the first or last record​

The #first and #last methods can be used to retrieve the first or last record for a given query set.

Author.filter(first_name: "John").first
Author.filter(first_name: "John").last

If the considered query set is empty, the returned value will be nil. It should be noted that these methods have a bang equivalent (#first! and #last!) that both raise a NilAssertionError if the query set is empty.

Field predicates​

Field predicates allow to define filters that are applied to a given query set. They map to WHERE clauses in the produced SQL queries.

For example:

Article.filter(title__icontains: "top")

Will translate to a SQL query like the following one (using PostgreSQL's syntax):

SELECT * FROM articles WHERE title LIKE UPPER("top")

Field predicates always contain a mandatory field name (title in the previous example) and an optional predicate type (icontains in the previous example). The field name and the predicate type are always separated by a double underscore notation (__). This notation (<field_name>__<predicate_type>) is used as the keyword argument name while the argument value is used to define the value to use to perform the filtering.

tip

The field name can correspond to any of the fields defined in the model being filtered. For many_to_one or one_to_one fields, it's possible to append a _id at the end of the field name to explicitly filter on the raw ID of the related record:

Article.all.filter(author_id: 42)

Marten support numerous predicate types, which are all documented in the field predicates reference. The ones that you'll encounter most frequently are outlined below:

exact​

The exact field predicate can be used for "exact" matches: only records whose field values exactly match the specified value will be returned. This is the default predicate type, and it's not necessary to specify it when filtering model records.

As such, the two following examples are equivalent:

Author.filter(first_name: "John")
Author.filter(first_name__exact: "John")

iexact​

This field predicate can be used for case insensitive matches.

For example, the following filter would return Article records whose titles are Test, TEST or test:

Article.filter(title__iexact: "test")

contains​

This field predicate can be used to filter strings that should contain a specific value. For example:

Article.filter(title__contains: "top")

A case insensitive equivalent (icontains) is also available.

Advanced querying capabilities​

Complex filters with q expressions​

As mentioned previously, field predicates expressed as keyword arguments will use an AND operator in the produced WHERE clauses. In order to produce conditions using other operators, it is necessary to use q expressions.

In order to produce such expressions, methods like #filter, #exclude, or #get can receive a block allowing to define complex conditions. Inside of this block, a #q method can be used in order to define conditions nodes that can be combined together using the following operators:

  • & in order to perform a logical "AND"
  • | in order to perform a logical "OR"
  • ^ in order to perform a logical "XOR"
  • - in order to perform a logical negation

For example, the following snippet will return all the Article records whose title starts with "Top" or "10":

Article.filter { q(title__startswith: "Top") | q(title__startswith: "10") }

Using this approach, it is possible to produce complex conditions by combining q() expressions with the &, |, ^, and - operators. Parentheses can also be used to group statements:

Article.filter {
  (q(title__startswith: "Top") | q(title__startswith: "10")) & -q(author__first_name: "John")
}

Finally it should be noted that you can define many field predicates inside q() expressions. When doing so, the field predicates will be "AND"ed together:

Article.filter {
  q(title__startswith: "Top") & -q(author__first_name: "John", author__last_name: "Doe")
}

Filtering relations​

The double underscores notation described previously (__) can also be used to filter based on related model fields. For example, in the considered models definitions, we have an Article model which defines a relation (many_to_one field) to the Author model through the author field. The Author model itself also defines a relation to a City record through the hometown field.

Given this data model, we could easily retrieve Article records whose author's first name is "John" with the following query set:

Article.filter(author__first_name: "John")

We could even retrieve all the Article records whose author are located in "Montréal" with the following query set:

Article.filter(author__hometown__name: "Montreal")

And obviously, the above query sets could also be used along with more specific field predicate types. For example:

Author.filter(author__hometown__name__startswith: "New")

When doing “deep filtering” like this, related model tables are automatically "joined" at the SQL level to perform the query (inner joins or left outer joins are used depending on the nullability of the filtered fields).

It is worth noting that this filtering capability also works for many-to-many relationships and reverse relations. For example, assuming that the Article model defines a tags many-to-many field towards a hypothetical Tag model, the following query would be possible:

Article.filter(tags__label: "crystal")

Pre-selecting relations with joins​

It is also possible to explicitly define that a specific query set must "join" a set of relations. This can result in nice performance improvements since this can help reduce the number of SQL queries performed for a given codebase. This is achieved through the use of the #join method:

author_1 = Author.filter(first_name: "John")
puts author_1.hometown # DB hit to retrieve the associated City record

author_2 = Author.join(:hometown).filter(first_name: "John")
puts author_2.hometown # No additional DB hit

The double underscores notations can also be used in the context of joins. For example:

# The associated Author and City records will be selected and fully initialized
# with the selected Article record.
Article.join(:author__hometown).get(id: 42)

Finally, it is worth mentioning that many relations can be specified to #join. For example:

Article.join(:author__hometown, :edited_by)
info

Please note that the #join query set method can only be used on many-to-one relationships, one-to-one relationships, and reverse one-to-one relations. For multi-valued relations, please consider pre-fetching records.

Pre-fetching relations​

While pre-selecting relations with joins can result in performance improvements (and help in reducing the number of SQL queries) by performing joins at the SQL level, is also possible to pre-fetch relations using the #prefetch method.

Both methods serve a common purpose, aiming to alleviate N+1 issues commonly encountered when accessing related objects. However, their strategies diverge in approach:

  • When using #join, the specified relationships are followed and each record returned by the considered query set has the corresponding related objects already selected and populated. The performance improvements are achieved by reducing the number of SQL queries since related records are retrieved by creating an SQL join and by including their fields in the main SELECT statement. Because of this, #join can only be used on single-valued relationships: many-to-one relationships, one-to-one relationships, and reverse one-to-one relations.
  • When using #prefetch, the records corresponding to the specified relationships will be prefetched in single batches and each record returned by the original query set will have the corresponding related objects already selected and populated. As such, #prefetch can be used with any kind of relationship: many-to-one relationships, one-to-one relationships, many-to-many relationships, and all types of reverse relations.

For example, assuming that a Post model defines a tags many-to-many field:

posts_1 = Post.all.to_a
# hits the database to retrieve the related "tags" (many-to-many relation)
puts posts_1[0].tags.to_a

posts_2 = Post.all.prefetch(:tags).to_a
# doesn't hit the database since the related "tags" relation was already prefetched
puts posts_2[0].tags.to_a

The double underscores notations can also be used when pre-fetching relations. In this situation, the records targeted by the original query set will be decorated with the prefetched records, and those records will be decorated with the following prefetched records. For example:

# The associated Book and BookGenres records will be pre-fetched and fully initialized
# at the Author and Book records levels.
Author.prefetch(:books__genres)

Finally, it is worth mentioning that multiple relations can be specified to #prefetch. For example:

Author.prefetch(:books__genres, :publisher)

Pagination​

Marten provides a pagination mechanism that you can leverage in order to easily iterate over records that are split across several pages of data. This works as follows: each query set object lets you generate a "paginator" (instance of Marten::DB::Query::Paginator) from a given page size (the number of records you would like on each page). You can then use this paginator in order to request specific pages, which gives you access to the corresponding records and to some additional pagination metadata.

For example:

query_set = Article.filter(published: true)

paginator = query_set.paginator(10)
paginator.page_size   # => 10
paginator.pages_count # => 6

# Retrieve the first page and iterate over the underlying records
page = paginator.page(1)
page.each { |article| puts article }
page.number               # 1
page.previous_page?       # => false
page.previous_page_number # => nil
page.next_page?           # => true
page.next_page_number     # => 2

As you can see, paginator objects let you request specific pages by providing a page number (1-indexed!) to the #page method. Such pages are instances of Marten::DB::Query::Page and give you the ability to easily iterate over the corresponding records. They also give you the ability to retrieve some pagination-related information (eg. about the previous and next pages by leveraging the #previous_page?, #previous_page_number, #next_page?, and #next_page_number methods).

Updating records​

Once a model record has been retrieved from the database, it is possible to update it by modifying its attributes and calling the #save method (already mentioned previously):

article = Article.get(id: 42)
article.title = "Updated!"
article.save

It is also possible to update records through the use of query sets. To do so, the #update method can be chained to a pre-defined query set in order to update all the resulting records:

Article.filter(title: "My article").update(title: "Updated!")

When calling the #update method like in the previous example, the update is done at the SQL level (using a regular UPDATE SQL statement) and the method returns the number of impacted records. As such it's important to remember that records updated like this won't be instantiated or validated before the update, and that no callbacks will be executed for them.

Deleting records​

Single model records that have been retrieved from the database can be deleted by using the #delete method:

article = Article.get(id: 42)
article.delete

Marten also provide the ability to delete the records that are targetted by a specific query set through the use of the #delete method, like in the following example:

Article.filter(title: "My article").delete

By default, related objects that are associated with the deleted records will also be deleted by following the deletion strategy defined in each relation field (on_delete option, see the reference for more details). The method always returns the number of deleted records.

Scopes​

Scopes allow for the pre-definition of specific filtered query sets, which can be easily applied to model classes and model query sets. When defining such scopes, all the query set capabilities that were covered previously (such as filtering records, excluding records, etc) can be leveraged.

Defining scopes​

Scopes can be defined through the use of the #scope macro. This macro expects a scope name (string literal or symbol) as first argument and requires a block where the query set filtering logic is defined.

For example:

class Post < Marten::Model
  field :id, :big_int, primary_key: true, auto: true
  field :title, :string, max_size: 255
  field :is_published, :bool, default: false
  field :created_at, :date_time

  scope :published { filter(is_published: true) }
  scope :unpublished { filter(is_published: false) }
  scope :recent { filter(created_at__gt: 1.year.ago) }
end

Considering the above model definition, it is possible to get published posts by using the following method call:

Post.published # => Post::QuerySet [...]>

Similarly, retrieving all published posts from a query set object can be accomplished by calling the #published method on the query set object:

query_set = Post.all
query_set.published # => Post::QuerySet [...]>

Because of this capability, it is important to note that scopes can technically be chained. For example, the following snippet will return all the published posts that were created less than one year ago:

Post.published.recent # => Post::QuerySet [...]>

Defining scopes with arguments​

If needed, you can define scopes that require arguments. To accomplish this, simply include the required arguments within the scope block.

For example:

class Post < Marten::Model
  field :id, :big_int, primary_key: true, auto: true
  field :title, :string, max_size: 255
  field :author, :many_to_one, to: Author

  scope :by_author_id { |author_id| filter(author_id: author_id) }
end

Scopes that require arguments can be used in the same way as argument-free scopes; they can be called on model classes or model query sets:

Post.by_author_id(42)      # => Post::QuerySet [...]>

query_set = Post.all
query_set.by_author_id(42) # => Post::QuerySet [...]>

Defining default scopes​

By default, querying all model records returns unfiltered query sets. However, you can define a default scope to automatically apply a specific filter to all queries for that model. This ensures that certain criteria are consistently enforced without the need to explicitly include a specific filter in every query.

Default scopes can be defined through the use of the #default_scope macro. This macro requires a block where the query set filtering logic is defined.

For example:

class Post < Marten::Model
  field :id, :big_int, primary_key: true, auto: true
  field :title, :string, max_size: 255
  field :is_published, :bool, default: false
  field :created_at, :date_time

  default_scope { filter(is_published: true) }
end

Disabling scoping​

It is worth mentioning that unscoped model records are always accessible through the use of the #unscoped class method. This is especially useful if your model defines a default scope and you need to override it for certain queries.

For example:

class Post < Marten::Model
  field :id, :big_int, primary_key: true, auto: true
  field :title, :string, max_size: 255
  field :is_published, :bool, default: false
  field :created_at, :date_time

  default_scope { filter(is_published: true) }
end

Considering, the above model definition, you can retrieve all the Post records by bypassing the default scope with:

Post.unscoped # => Post::QuerySet [...]>