Finding Records

The finder methods for DataMapper objects are defined in DataMapper::Repository. They include #get, #all, #first, #last

Finder Methods

DataMapper has methods which allow you to grab a single record by key, the first match to a set of conditions, or a collection of records matching conditions.

1 zoo  = Zoo.get(1)                     # get the zoo with primary key of 1.
2 zoo  = Zoo.get!(1)                    # Or get! if you want an ObjectNotFoundError on failure
3 zoo  = Zoo.get('DFW')                 # wow, support for natural primary keys
4 zoo  = Zoo.get('Metro', 'DFW')        # more wow, composite key look-up
5 zoo  = Zoo.first(:name => 'Metro')    # first matching record with the name 'Metro'
6 zoo  = Zoo.last(:name => 'Metro')     # last matching record with the name 'Metro'
7 zoos = Zoo.all                        # all zoos
8 zoos = Zoo.all(:open => true)         # all zoos that are open
9 zoos = Zoo.all(:opened_on => (s..e))  # all zoos that opened on a date in the date-range

Scopes and Chaining

Calls to `#all can be chained together to further build a query to the data-store:

1 all_zoos      = Zoo.all
2 open_zoos     = all_zoos.all(:open => true)
3 big_open_zoos = open_zoos.all(:animal_count => 1000)

As a direct consequence, you can define scopes without any extra work in your model.

 1 class Zoo
 2   # all the keys and property setup here
 3   def self.open
 4     all(:open => true)
 5   end
 6 
 7   def self.big
 8     all(:animal_count => 1000)
 9   end
10 end
11 
12 big_open_zoos = Zoo.big.open

Scopes like this can even have arguments. Do anything in them, just ensure they return a Query of some kind.

Conditions

Rather than defining conditions using SQL fragments, we can actually specify conditions using a hash.

The examples above are pretty simple, but you might be wondering how we can specify conditions beyond equality without resorting to SQL. Well, thanks to some clever additions to the Symbol class, it's easy!

1 exhibitions = Exhibition.all(:run_time.gt => 2, :run_time.lt => 5)
2 # => SQL conditions: 'run_time > 2 AND run_time < 5'

Valid symbol operators for the conditions are:

1 gt    # greater than
2 lt    # less than
3 gte   # greater than or equal
4 lte   # less than or equal
5 not   # not equal
6 eql   # equal
7 like  # like

Nested Conditions

DataMapper allows you to create and search for any complex object graph simply by providing a nested hash of conditions.

Possible keys are all property and relationship names (as symbols or strings) that are established in the model the current nesting level points to. The available toplevel keys depend on the model the conditions hash is passed to. We'll see below how to change the nesting level and thus the model the property and relationship keys are scoped to.

For property name keys, possible values typically are simple objects like strings, numbers, dates or booleans. Using properties as keys doesn't add another nesting level.

For relationship name keys, possible values are either a hash (if the relationship points to a single resource) or an array of hashes (if the relationship points to many resources). Adding a relationship name as key adds another nesting level scoped to the Model the relationship is pointing to. Inside this new level, the available keys are the property and relationship names of the model that the relationship points to. This is what we meant by "the Model the current nesting level points to".

The following example shows a typical Customer - Order domain model and illustrates how nested conditions can be used to both create and search for specific resources.

 1 class Customer
 2   include DataMapper::Resource
 3 
 4   property :id,   Serial
 5   property :name, String, :required => true, :length => 1..100
 6 
 7   has n, :orders
 8   has n, :items, :through => :orders
 9 end
10 
11 class Order
12   include DataMapper::Resource
13 
14   property :id,        Serial
15   property :reference, String, :required => true, :length => 1..20
16 
17   belongs_to :customer
18 
19   has n, :order_lines
20   has n, :items, :through => :order_lines
21 end
22 
23 class OrderLine
24   include DataMapper::Resource
25 
26   property :id,         Serial
27   property :quantity,   Integer, :required => true, :default => 1, :min => 1
28   property :unit_price, Decimal, :required => true, :default => lambda { |r, p| r.item.unit_price }
29 
30   belongs_to :order
31   belongs_to :item
32 end
33 
34 class Item
35   include DataMapper::Resource
36 
37   property :id,         Serial
38   property :sku,        String,  :required => true, :length => 1..20
39   property :unit_price, Decimal, :required => true, :min => 0
40 
41   has n, :order_lines
42 end
43 
44 # A hash specifying a customer with one order
45 customer = {
46   :name   => 'Dan Kubb',
47   :orders => [
48     {
49       :reference   => 'TEST1234',
50       :order_lines => [
51         {
52           :item => {
53             :sku        => 'BLUEWIDGET1',
54             :unit_price => 1.00,
55           },
56         },
57       ],
58     },
59   ]
60 }
61 
62 # Create the Customer with the nested options hash
63 Customer.create(customer)
64 
65 # The options to create can also be used to retrieve the same object
66 p Customer.all(customer)
67 
68 # QueryPaths can be used to construct joins in a very declarative manner.
69 #
70 # Starting from a root model, you can call any relationship by its name.
71 # The returned object again responds to all property and relationship names
72 # that are defined in the relationship's target model.
73 #
74 # This means that you can walk the chain of available relationships, and then
75 # match against a property at the end of that chain. The object returned by
76 # the last call to a property name also responds to all the comparison
77 # operators available in traditional queries. This makes for some powerful
78 # join construction!
79 #
80 Customer.all(Customer.orders.order_lines.item.sku.like => "%BLUE%")
81 # => [#<Customer @id=1 @name="Dan Kubb">]

Order

To specify the order in which your results are to be sorted, use:

1 @zoos_by_tiger_count = Zoo.all(:order => [ :tiger_count.desc ])
2 # in SQL => SELECT * FROM "zoos" ORDER BY "tiger_count" DESC

Available order vectors are:

1 asc   # sorting ascending
2 desc  # sorting descending

Once you have the query, the order can be modified too. Just call reverse:

1 @least_tigers_first = @zoos_by_tiger_count.reverse
2 # in SQL => SELECT * FROM "zoos" ORDER BY "tiger_count" ASC

Ranges

If you have guaranteed the order of a set of results, you might choose to only use the first ten results, like this.

1 @zoos_by_tiger_count = Zoo.all(:limit => 10, :order => [ :tiger_count.desc ])

Or maybe you wanted the fifth set of ten results.

1 @zoos_by_tiger_count = Zoo.all(:offset => 40, :limit => 10, :order => [ :tiger_count.desc ])

Combining Queries

Sometimes, the simple queries DataMapper allows you to specify with the hash interface to #all just won't cut it. This might be because you want to specify an OR condition, though that's just one possibility. To accomplish more complex queries, DataMapper allows queries (or more accurately, Collections) to be combined using set operators.

 1 # Find all Zoos in Illinois, or those with five or more tigers
 2 Zoo.all(:state => 'IL') + Zoo.all(:tiger_count.gte => 5)
 3 # in SQL => SELECT * FROM "zoos" WHERE ("state" = 'IL' OR "tiger_count" >= 5)
 4 
 5 # It also works with the union operator
 6 Zoo.all(:state => 'IL') | Zoo.all(:tiger_count.gte => 5)
 7 # in SQL => SELECT * FROM "zoos" WHERE ("state" = 'IL' OR "tiger_count" >= 5)
 8 
 9 # Intersection produces an AND query
10 Zoo.all(:state => 'IL') & Zoo.all(:tiger_count.gte => 5)
11 # in SQL => SELECT * FROM "zoos" WHERE ("state" = 'IL' AND "tiger_count" >= 5)
12 
13 # Subtraction produces a NOT query
14 Zoo.all(:state => 'IL') - Zoo.all(:tiger_count.gte => 5)
15 # in SQL => SELECT * FROM "zoos" WHERE ("state" = 'IL' AND NOT("tiger_count" >= 5))

Of course, the latter two queries could be achieved using the standard symbol operators. Set operators work on any Collection though, and so Zoo.all(:state => 'IL') could just as easily be replaced with Zoo.open.big or any other method which returns a collection.

Projecting only specific properties

In order to not select all of your model's properties but only a subset of them, you can pass :fields => [:desired, :property, :names] in your queries.

 1 # Will return a mutable collection of zoos
 2 Zoo.all(:fields => [:id, :name])
 3 
 4 # Will return an immutable collection of zoos.
 5 # The collection is immutable because we haven't
 6 # projected the primary key of the model.
 7 # DataMapper will raise DataMapper::ImmutableError
 8 # when trying to modify any resource inside the
 9 # returned collection.
10 Zoo.all(:fields => [:name])

Compatibility

DataMapper supports other conditions syntaxes as well:

1 zoos = Zoo.all(:conditions => { :id => 34 })
2 
3 # You can use this syntax to call native storage engine functions
4 zoos = Zoo.all(:conditions => [ 'id = ?', 34 ])
5 
6 # even mix and match
7 zoos = Zoo.all(:conditions => { :id => 34 }, :name.like => '%foo%')

Talking directly to your data-store

Sometimes you may find that you need to tweak a query manually.

1 zoos = repository(:default).adapter.select('SELECT name, open FROM zoos WHERE open = 1')
2 #      Note that this will not return Zoo objects, rather the raw data straight from the database

zoos will be full of Struct objects with name, and open attributes, rather than instances of the Zoo class. They'll also be read-only. You can still use the interpolated array condition syntax as well:

1 zoos = repository(:default).adapter.select('SELECT name, open FROM zoos WHERE name = ?', 'Awesome Zoo')

Grouping

DataMapper automatically groups by all selected columns in order to return consistent results across various datastores. If you need to group by some columns explicitly, you can use the :fields combined with the :unique option.

 1 class Person
 2   include DataMapper::Resource
 3   property :id,  Serial
 4   property :job, String
 5 end
 6 
 7 Person.auto_migrate!
 8 
 9 # Note that if you don't include the primary key, you will need to
10 # specify an explicit order vector, because DM will default to the
11 # primary key if it's not told otherwise (at least currently).
12 # PostgreSQL will present this rather informative error message when
13 # you leave out the order vector in the query below.
14 #
15 #   column "people.id" must appear in the GROUP BY clause
16 #   or be used in an aggregate function
17 #
18 # To not do any ordering, you would need to provide :order => nil
19 #
20 Person.all(:fields => [:job], :unique => true, :order => [:job.asc])
21 # ...
22 # SELECT "job" FROM "people" GROUP BY "job" ORDER BY "job"

Note that if you don't include the primary key in the selected columns, you will not be able to modify the returned resources because DataMapper cannot know how to persist them. DataMapper will raise DataMapper::ImmutableError if you're trying to do so nevertheless.

If a group by isn't appropriate and you're rather looking for select distinct, you need to drop down to talking to your datastore directly, as shown in the section above.

Aggregate functions

For the following to work, you need to have dm-aggregates required.

Counting

1 Friend.count # returns count of all friends
2 Friend.count(:age.gt => 18) # returns count of all friends older then 18
3 Friend.count(:conditions => [ 'gender = ?', 'female' ]) # returns count of all your female friends
4 Friend.count(:address) # returns count of all friends with an address (NULL values are not included)
5 Friend.count(:address, :age.gt => 18) # returns count of all friends with an address that are older then 18
6 Friend.count(:address, :conditions => [ 'gender = ?', 'female' ]) # returns count of all your female friends with an address

Minimum and Maximum

1 # Get the lowest value of a property
2 Friend.min(:age) # returns the age of the youngest friend
3 Friend.min(:age, :conditions => [ 'gender = ?', 'female' ]) # returns the age of the youngest female friends
4 # Get the highest value of a property
5 Friend.max(:age) # returns the age of the oldest friend
6 Friend.max(:age, :conditions => [ 'gender = ?', 'female' ]) # returns the age of the oldest female friends

Average and Sum

1 # Get the average value of a property
2 Friend.avg(:age) # returns the average age of friends
3 Friend.avg(:age, :conditions => [ 'gender = ?', 'female' ]) # returns the average age of the female friends
4 
5 # Get the total value of a property
6 Friend.sum(:age) # returns total age of all friends
7 Friend.sum(:age, :conditions => [ 'gender = ?', 'female' ]) # returns the total age of all female friends

Multiple aggregates

1 sum, count = Friend.aggregate(:age.sum, :all.count) # returns the sum of all ages and the count of all friends

Aggregates with order-by

1 Friend.aggregate(:city, :all.count) # returns the city names and the number of friends living in each city
2 # e.g. [['Hamburg', 3], ['New York', 4], ['Rome', 0], ... ]