class Marten::DB::Query::Set(M)

Overview

The main query set class.

A query set is an object that matches a collection of records in the database. Those objects are matched through the use of optional filters that allow to explicitly query the database based on specific parameters. Query sets also allow to configure how these objects should be returned (for example in which order).

The most important characteristic of a query set is that it is lazily evaluated: unless the code that resulted in the creation of the query set explicitly asks for the underlying objects, no actual query is made to the considered database. Querying the database is always deferred to the last possible moment: that is, when the actual records are requested.

Included Modules

Direct Known Subclasses

Defined in:

marten/db/query/set.cr
marten/template/ext/db/query/set.cr

Constructors

Instance Method Summary

Macros inherited from module Marten::Template::Object

template_attributes(*names) template_attributes

Constructor Detail

def self.new(query : Marten::DB::Query::SQL::Query(M) = SQL::Query(M).new) #

[View source]

Instance Method Detail

def [](index : Int) #

Returns the record at the given index.

If no record can be found at the given index, then an IndexError exception is raised.


[View source]
def [](range : Range) #

Returns the records corresponding to the passed range.

If no records match the passed range, an IndexError exception is raised. If the current query set was already "consumed" (records were retrieved from the database), an array of records will be returned. Otherwise, another sliced query set will be returned.


[View source]
def []?(index : Int) #

Returns the record at the given index.

nil is returned if no record can be found at the given index.


[View source]
def []?(range : Range) #

Returns the records corresponding to the passed range.

nil is returned if no records match the passed range. If the current query set was already "consumed" (records were retrieved from the database), an array of records will be returned. Otherwise, another sliced query set will be returned.


[View source]
def all #

Returns a cloned version of the current query set matching all records.


[View source]
def any? #

Returns true if the query set matches at least one record, or false otherwise. Alias for #exists?.


[View source]
def count #

Returns the number of records that are targeted by the current query set.


[View source]
def create(**kwargs) #

Creates a model instance and saves it to the database if it is valid.

The new model instance is initialized by using the attributes defined in the kwargs double splat argument. Regardless of whether it is valid or not (and thus persisted to the database or not), the initialized model instance is returned by this method:

query_set = Post.all
query_set.create(title: "My blog post")

[View source]
def create(**kwargs, &) #

Creates a model instance and saves it to the database if it is valid.

This method provides the exact same behaviour as #create with the ability to define a block that is executed for the new object. This block can be used to directly initialize the object before it is persisted to the database:

query_set = Post.all
query_set.create(title: "My blog post") do |post|
  post.complex_attribute = compute_complex_attribute
end

[View source]
def create!(**kwargs) #

Creates a model instance and saves it to the database if it is valid.

The model instance is initialized using the attributes defined in the kwargs double splat argument. If the model instance is valid, it is persisted to the database ; otherwise a Marten::DB::Errors::InvalidRecord exception is raised.

query_set = Post.all
query_set.create!(title: "My blog post")

[View source]
def create!(**kwargs, &) #

Creates a model instance and saves it to the database if it is valid.

This method provides the exact same behaviour as #create! with the ability to define a block that is executed for the new object. This block can be used to directly initialize the object before it is persisted to the database:

query_set = Post.all
query_set.create!(title: "My blog post") do |post|
  post.complex_attribute = compute_complex_attribute
end

[View source]
def delete(raw : Bool = false) : Int64 #

Deletes the records corresponding to the current query set and returns the number of deleted records.

By default, related objects will be deleted by following the deletion strategy defined in each foreign key field if applicable, unless the #raw argument is set to true.

When the #raw argument is set to true, a raw SQL delete statement will be used to delete all the records matching the currently applied filters. Note that using this option could cause errors if the underlying database enforces referential integrity.


[View source]
def distinct #

Returns a new query set that will use SELECT DISTINCT in its query.

By doing so it is possible to eliminate any duplicated row in the query set results:

query_set = Post.all.distinct

[View source]
def distinct(*fields : String | Symbol) #

Returns a new query set that will use SELECT DISTINCT ON in its query

By doing so it is possible to eliminate any duplicated row based on the specified fields:

query_set = Post.all.distinct(:title)

It should be noted that it is also possible to follow associations of direct related models too by using the double underscores notation(__). For example the following query will select distinct records based on a joined "author" attribute:

query_set = Post.all.distinct(:author__name)

[View source]
def each(&) #

Allows to iterate over the records that are targeted by the current query set.

This method can be used to define a block that iterates over the records that are targeted by a query set:

Post.all.each do |post|
  # Do something
end

[View source]
def exclude(query_node : Node) #

Returns a query set whose records do not match the given query node object.


[View source]
def exclude(**kwargs) #

Returns a query set whose records do not match the given set of filters.

This method returns a Marten::DB::Query::Set object. The filters passed to this method method must be specified using the predicate format:

query_set = Post.all
query_set.exclude(title: "Test")
query_set.exclude(title__startswith: "A")

If multiple filters are specified, they will be joined using an AND operator at the SQL level.


[View source]
def exclude(&) #

Returns a query set whose records do not match the given set of advanced filters.

This method returns a Marten::DB::Query::Set object and allows to define complex database queries involving AND and OR operators. It yields a block where each filter has to be wrapped using a q(...) expression. These expressions can then be used to build complex queries such as:

query_set = Post.all
query_set.exclude { (q(name: "Foo") | q(name: "Bar")) & q(is_published: True) }

[View source]
def exists?(query_node : Node) #

Returns true if the a query set filtered with the given query node object matches at least one record.

query_set = Post.all
query_set.exists?(Marten::DB::Query::Node.new(name__startswith: "Fr"))

[View source]
def exists? #

Returns true if the current query set matches at least one record, or false otherwise.


[View source]
def exists?(**kwargs) #

Returns true if the query set corresponding to the specified filters matches at least one record.

This method returns true if the filters passed to this method match at least one record. These filters must be specified using the predicate format:

query_set = Post.all
query_set.exists?(title: "Test")
query_set.exists?(title__startswith: "A")

If multiple filters are specified, they will be joined using an AND operator at the SQL level.


[View source]
def exists?(&) #

Returns true if the query set corresponding to the specified advanced filters matches at least one record.

This method returns a Bool object and allows to define complex database queries involving AND and OR operators. It yields a block where each filter has to be wrapped using a q(...) expression. These expressions can then be used to build complex queries such as:

query_set = Post.all
query_set.exists? { (q(name: "Foo") | q(name: "Bar")) & q(is_published: true) }

[View source]
def filter(query_node : Node) #

Returns a query set whose records match the given query node object.


[View source]
def filter(**kwargs) #

Returns a query set matching a specific set of filters.

This method returns a Marten::DB::Query::Set object. The filters passed to this method method must be specified using the predicate format:

query_set = Post.all
query_set.filter(title: "Test")
query_set.filter(title__startswith: "A")

If multiple filters are specified, they will be joined using an AND operator at the SQL level.


[View source]
def filter(&) #

Returns a query set matching a specific set of advanced filters.

This method returns a Marten::DB::Query::Set object and allows to define complex database queries involving AND and OR operators. It yields a block where each filter has to be wrapped using a q(...) expression. These expressions can then be used to build complex queries such as:

query_set = Post.all
query_set.filter { (q(name: "Foo") | q(name: "Bar")) & q(is_published: true) }

[View source]
def first #

Returns the first record that is matched by the query set, or nil if no records are found.


[View source]
def first! #

Returns the first record that is matched by the query set, or raises a NilAssertionError error otherwise.


[View source]
def get(query_node : Node) #

Returns the model instance matching a specific query node object, or nil if no record is found.


[View source]
def get(**kwargs) #

Returns the model instance matching the given set of filters.

Model fields such as primary keys or fields with a unique constraint should be used here in order to retrieve a specific record:

query_set = Post.all
post_1 = query_set.get(id: 123)
post_2 = query_set.get(id: 456, is_published: false)

If the specified set of filters doesn't match any records, the returned value will be nil.

In order to ensure data consistency, this method will raise a Marten::DB::Errors::MultipleRecordsFound exception if multiple records match the specified set of filters.


[View source]
def get(&) #

Returns the model instance matching a specific set of advanced filters.

Model fields such as primary keys or fields with a unique constraint should be used here in order to retrieve a specific record:

query_set = Post.all
post_1 = query_set.get { q(id: 123) }
post_2 = query_set.get { q(id: 456, is_published: false) }

If the specified set of filters doesn't match any records, the returned value will be nil.

In order to ensure data consistency, this method will raise a Marten::DB::Errors::MultipleRecordsFound exception if multiple records match the specified set of filters.


[View source]
def get!(query_node : Node) #

Returns the model instance matching a specific query node object, or raise an error otherwise.


[View source]
def get!(**kwargs) #

Returns the model instance matching the given set of filters.

Model fields such as primary keys or fields with a unique constraint should be used here in order to retrieve a specific record:

query_set = Post.all
post_1 = query_set.get!(id: 123)
post_2 = query_set.get!(id: 456, is_published: false)

If the specified set of filters doesn't match any records, a Marten::DB::Errors::RecordNotFound exception will be raised.

In order to ensure data consistency, this method will also raise a Marten::DB::Errors::MultipleRecordsFound exception if multiple records match the specified set of filters.


[View source]
def get!(&) #

Returns the model instance matching a specific set of advanced filters.

Model fields such as primary keys or fields with a unique constraint should be used here in order to retrieve a specific record:

query_set = Post.all
post_1 = query_set.get! { q(id: 123) }
post_2 = query_set.get! { q(id: 456, is_published: false) }

If the specified set of filters doesn't match any records, a Marten::DB::Errors::RecordNotFound exception will be raised.

In order to ensure data consistency, this method will raise a Marten::DB::Errors::MultipleRecordsFound exception if multiple records match the specified set of filters.


[View source]
def get_or_create(**kwargs) #

Returns the model record matching the given set of filters or create a new one if no one is found.

Model fields that uniquely identify a record should be used here. For example:

tag = Tag.all.get_or_create(label: "crystal")

When no record is found, the new model instance is initialized by using the attributes defined in the kwargs double splat argument. Regardless of whether it is valid or not (and thus persisted to the database or not), the initialized model instance is returned by this method.

In order to ensure data consistency, this method will raise a Marten::DB::Errors::MultipleRecordsFound exception if multiple records match the specified set of filters.


[View source]
def get_or_create(**kwargs, &) #

Returns the model record matching the given set of filters or create a new one if no one is found.

Model fields that uniquely identify a record should be used here. The provided block can be used to initialize the model instance to create (in case no record is found). For example:

tag = Tag.all.get_or_create(label: "crystal") do |new_tag|
  new_tag.active = false
end

When no record is found, the new model instance is initialized by using the attributes defined in the kwargs double splat argument. Regardless of whether it is valid or not (and thus persisted to the database or not), the initialized model instance is returned by this method.

In order to ensure data consistency, this method will raise a Marten::DB::Errors::MultipleRecordsFound exception if multiple records match the specified set of filters.


[View source]
def get_or_create!(**kwargs) #

Returns the model record matching the given set of filters or create a new one if no one is found.

Model fields that uniquely identify a record should be used here. For example:

tag = Tag.all.get_or_create!(label: "crystal")

When no record is found, the new model instance is initialized by using the attributes defined in the kwargs double splat argument. If the new model instance is valid, it is persisted to the database ; otherwise a Marten::DB::Errors::InvalidRecord exception is raised.

In order to ensure data consistency, this method will raise a Marten::DB::Errors::MultipleRecordsFound exception if multiple records match the specified set of filters.


[View source]
def get_or_create!(**kwargs, &) #

Returns the model record matching the given set of filters or create a new one if no one is found.

Model fields that uniquely identify a record should be used here. The provided block can be used to initialize the model instance to create (in case no record is found). For example:

tag = Tag.all.get_or_create!(label: "crystal") do |new_tag|
  new_tag.active = false
end

When no record is found, the new model instance is initialized by using the attributes defined in the kwargs double splat argument. If the new model instance is valid, it is persisted to the database ; otherwise a Marten::DB::Errors::InvalidRecord exception is raised.

In order to ensure data consistency, this method will raise a Marten::DB::Errors::MultipleRecordsFound exception if multiple records match the specified set of filters.


[View source]
def includes?(value : M) #

Returns true if a specific model record is included in the query set.


[View source]
def inspect(io) #

Appends a string representation of the query set to the passed io.


[View source]
def join(*relations : String | Symbol) #

Returns a queryset whose specified relations are "followed" and joined to each result.

When using #join, the specified foreign-key relationships will be followed and each record returned by the queryset will have the corresponding related objects already selected and populated. Using #join can result in performance improvements since it can help reduce the number of SQL queries, as illustrated by the following example:

query_set = Post.all

p1 = query_set.get(id: 1)
puts p1.author # hits the database to retrieve the related "author"

p2 = query_set.join(:author).get(id: 1)
puts p2.author # doesn't hit the database since the related "author" was already selected

It should be noted that it is also possible to follow foreign keys of direct related models too by using the double underscores notation(__). For example the following query will select the joined "author" and its associated "profile":

query_set = Post.all
query_set.join(:author__profile)

[View source]
def last #

Returns the last record that is matched by the query set, or nil if no records are found.


[View source]
def last! #

Returns the last record that is matched by the query set, or raises a NilAssertionError error otherwise.


[View source]
def model #

Returns the model class associated with the query set.


[View source]
def none #

Returns a queryset that will always return an empty array of record, without querying the database.

Once this method is used, any subsequent method call (such as extra filters) will continue returning an empty array of records.


[View source]
def order(fields : Array(String | Symbol)) #

Allows to specify the ordering in which records should be returned when evaluating the query set.

Multiple fields can be specified in order to define the final ordering. For example:

query_set = Post.all
query_set.order(["-published_at", "title"])

In the above example, records would be ordered by descending publication date, and then by title (ascending).


[View source]
def order(*fields : String | Symbol) #

Allows to specify the ordering in which records should be returned when evaluating the query set.

Multiple fields can be specified in order to define the final ordering. For example:

query_set = Post.all
query_set.order("-published_at", "title")

In the above example, records would be ordered by descending publication date, and then by title (ascending).


[View source]
def paginator(page_size : Int) #

Returns a paginator that can be used to paginate the current query set.

This method returns a Marten::DB::Query::Paginator object, which can then be used to retrieve specific pages.


[View source]
def pick(fields : Array(String | Symbol)) : Array(Field::Any) | Nil #

Returns specific column values for a single record without actually loading it.

This method allows to easily select specific column values for a single record from the current query set. This allows retrieving specific column values without actually loading the entire record, and as such this is most useful for query sets that have been narrowed down to match a single record. The method returns an array containing the requested column values, or nil if no record was matched by the current query set. For example:

Post.filter(pk: 1).pick(["title", "published"])
# => ["First article", true]

[View source]
def pick(*fields : String | Symbol) : Array(Field::Any) | Nil #

Returns specific column values for a single record without actually loading it.

This method allows to easily select specific column values for a single record from the current query set. This allows retrieving specific column values without actually loading the entire record, and as such this is most useful for query sets that have been narrowed down to match a single record. The method returns an array containing the requested column values, or nil if no record was matched by the current query set. For example:

Post.filter(pk: 1).pick("title", "published")
# => ["First article", true]

[View source]
def pick!(fields : Array(String | Symbol)) : Array(Field::Any) #

Returns specific column values for a single record without actually loading it.

This method allows to easily select specific column values for a single record from the current query set. This allows retrieving specific column values without actually loading the entire record, and as such this is most useful for query sets that have been narrowed down to match a single record. The method returns an array containing the requested column values, or raises NilAssertionError if no record was matched by the current query set. For example:

Post.filter(pk: 1).pick!(["title", "published"])
# => ["First article", true]

[View source]
def pick!(*fields : String | Symbol) : Array(Field::Any) #

Returns specific column values for a single record without actually loading it.

This method allows to easily select specific column values for a single record from the current query set. This allows retrieving specific column values without actually loading the entire record, and as such this is most useful for query sets that have been narrowed down to match a single record. The method returns an array containing the requested column values, or raises NilAssertionError if no record was matched by the current query set. For example:

Post.filter(pk: 1).pick!("title", "published")
# => ["First article", true]

[View source]
def pluck(fields : Array(String | Symbol)) : Array(Array(Field::Any)) #

Returns specific column values without loading entire record objects.

This method allows to easily select specific column values from the current query set. This allows retrieving specific column values without actually loading entire records. The method returns an array containing one array with the actual column values for each record targeted by the query set. For example:

Post.all.pluck(["title", "published"])
# => [["First article", true], ["Upcoming article", false]]

[View source]
def pluck(*fields : String | Symbol) : Array(Array(Field::Any)) #

Returns specific column values without loading entire record objects.

This method allows to easily select specific column values from the current query set. This allows retrieving specific column values without actually loading entire records. The method returns an array containing one array with the actual column values for each record targeted by the query set. For example:

Post.all.pluck("title", "published")
# => [["First article", true], ["Upcoming article", false]]

[View source]
def raw(query : String, params : Array) #

Returns a raw query set for the passed SQL query and positional parameters.

This method returns a Marten::DB::Query::RawSet object, which allows to iterate over the model records matched by the passed SQL query and associated positional parameters. For example:

Article.all.raw("SELECT * FROM articles WHERE title = ? and created_at > ?", ["Hello World!", "2022-10-30"])

[View source]
def raw(query : String, params : Hash | NamedTuple) #

Returns a raw query set for the passed SQL query and named parameters.

This method returns a Marten::DB::Query::RawSet object, which allows to iterate over the model records matched by the passed SQL query and associated named parameters. For example:

Article.all.raw(
  "SELECT * FROM articles WHERE title = :title and created_at > :created_at",
  {
    title:      "Hello World!",
    created_at: "2022-10-30",
  }
)

[View source]
def raw(query : String, *args) #

Returns a raw query set for the passed SQL query and optional positional parameters.

This method returns a Marten::DB::Query::RawSet object, which allows to iterate over the model records matched by the passed SQL query. For example:

Article.all.raw("SELECT * FROM articles")

Additional positional parameters can also be specified if the query needs to be parameterized. For example:

Article.all.raw("SELECT * FROM articles WHERE title = ? and created_at > ?", "Hello World!", "2022-10-30")

[View source]
def raw(query : String, **kwargs) #

Returns a raw query set for the passed SQL query and optional named parameters.

This method returns a Marten::DB::Query::RawSet object, which allows to iterate over the model records matched by the passed SQL query. For example:

Article.all.raw("SELECT * FROM articles")

Additional named parameters can also be specified if the query needs to be parameterized. For example:

Article.all.raw(
  "SELECT * FROM articles WHERE title = :title and created_at > :created_at",
  title: "Hello World!",
  created_at: "2022-10-30"
)

[View source]
def reverse #

Allows to reverse the order of the current query set.


[View source]
def size #

Returns the number of records that are targeted by the current query set.


[View source]
def to_s(io) #

Appends a string representation of the query set to the passed io.


[View source]
def update(values : Hash | NamedTuple) #

Updates all the records matched by the current query set with the passed values.

This method allows to update all the records that are matched by the current query set with a hash or a named tuple of values. It returns the number of records that were updated:

query_set = Post.all
query_set.update({"title" => "Updated"})

It should be noted that this methods results in a regular UPDATE SQL statement. As such, the records that are updated through the use of this method won't be validated, and no callbacks will be executed for them either.


[View source]
def update(**kwargs) #

Updates all the records matched by the current query set with the passed values.

This method allows to update all the records that are matched by the current query set with the values defined in the kwargs double splat argument. It returns the number of records that were updated:

query_set = Post.all
query_set.update(title: "Updated")

It should be noted that this methods results in a regular UPDATE SQL statement. As such, the records that are updated through the use of this method won't be validated, and no callbacks will be executed for them either.


[View source]
def using(db : Nil | String | Symbol) #

Allows to define which database alias should be used when evaluating the query set.


[View source]