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
2
3
4
5
6
7
8
9
zoo  = Zoo.get(1)                     # get the zoo with primary key of 1.
zoo  = Zoo.get!(1)                    # Or get! if you want an ObjectNotFoundError on failure
zoo  = Zoo.get('DFW')                 # wow, support for natural primary keys
zoo  = Zoo.get('Metro', 'DFW')        # more wow, composite key look-up
zoo  = Zoo.first(:name => 'Metro')    # first matching record with the name 'Metro'
zoo  = Zoo.last(:name => 'Metro')     # last matching record with the name 'Metro'
zoos = Zoo.all                        # all zoos
zoos = Zoo.all(:open => true)         # all zoos that are open
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
2
3
all_zoos      = Zoo.all
open_zoos     = all_zoos.all(:open => true)
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
2
3
4
5
6
7
8
9
10
11
12
class Zoo
  # all the keys and property setup here
  def self.open
    all(:open => true)
  end

  def self.big
    all(:animal_count => 1000)
  end
end

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
2
exhibitions = Exhibition.all(:run_time.gt => 2, :run_time.lt => 5)
# => SQL conditions: 'run_time > 2 AND run_time < 5'

Valid symbol operators for the conditions are:

1
2
3
4
5
6
7
gt    # greater than
lt    # less than
gte   # greater than or equal
lte   # less than or equal
not   # not equal
eql   # equal
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
class Customer
  include DataMapper::Resource

  property :id,   Serial
  property :name, String, :required => true, :length => 1..100

  has n, :orders
  has n, :items, :through => :orders
end

class Order
  include DataMapper::Resource

  property :id,        Serial
  property :reference, String, :required => true, :length => 1..20

  belongs_to :customer

  has n, :order_lines
  has n, :items, :through => :order_lines
end

class OrderLine
  include DataMapper::Resource

  property :id,         Serial
  property :quantity,   Integer, :required => true, :default => 1, :min => 1
  property :unit_price, Decimal, :required => true, :default => lambda { |r, p| r.item.unit_price }

  belongs_to :order
  belongs_to :item
end

class Item
  include DataMapper::Resource

  property :id,         Serial
  property :sku,        String,  :required => true, :length => 1..20
  property :unit_price, Decimal, :required => true, :min => 0

  has n, :order_lines
end

# A hash specifying a customer with one order
customer = {
  :name   => 'Dan Kubb',
  :orders => [
    {
      :reference   => 'TEST1234',
      :order_lines => [
        {
          :item => {
            :sku        => 'BLUEWIDGET1',
            :unit_price => 1.00,
          },
        },
      ],
    },
  ]
}

# Create the Customer with the nested options hash
Customer.create(customer)

# The options to create can also be used to retrieve the same object
p Customer.all(customer)

# QueryPaths can be used to construct joins in a very declarative manner.
#
# Starting from a root model, you can call any relationship by its name.
# The returned object again responds to all property and relationship names
# that are defined in the relationship's target model.
#
# This means that you can walk the chain of available relationships, and then
# match against a property at the end of that chain. The object returned by
# the last call to a property name also responds to all the comparison
# operators available in traditional queries. This makes for some powerful
# join construction!
#
Customer.all(Customer.orders.order_lines.item.sku.like => "%BLUE%")
# => [#<Customer @id=1 @name="Dan Kubb">]

Order

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

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

Available order vectors are:

1
2
asc   # sorting ascending
desc  # sorting descending

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

1
2
@least_tigers_first = @zoos_by_tiger_count.reverse
# 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Find all Zoos in Illinois, or those with five or more tigers
Zoo.all(:state => 'IL') + Zoo.all(:tiger_count.gte => 5)
# in SQL => SELECT * FROM "zoos" WHERE ("state" = 'IL' OR "tiger_count" >= 5)

# It also works with the union operator
Zoo.all(:state => 'IL') | Zoo.all(:tiger_count.gte => 5)
# in SQL => SELECT * FROM "zoos" WHERE ("state" = 'IL' OR "tiger_count" >= 5)

# Intersection produces an AND query
Zoo.all(:state => 'IL') & Zoo.all(:tiger_count.gte => 5)
# in SQL => SELECT * FROM "zoos" WHERE ("state" = 'IL' AND "tiger_count" >= 5)

# Subtraction produces a NOT query
Zoo.all(:state => 'IL') - Zoo.all(:tiger_count.gte => 5)
# 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
2
3
4
5
6
7
8
9
10
# Will return a mutable collection of zoos
Zoo.all(:fields => [:id, :name])

# Will return an immutable collection of zoos.
# The collection is immutable because we haven't
# projected the primary key of the model.
# DataMapper will raise DataMapper::ImmutableError
# when trying to modify any resource inside the
# returned collection.
Zoo.all(:fields => [:name])

Compatibility

DataMapper supports other conditions syntaxes as well:

1
2
3
4
5
6
7
zoos = Zoo.all(:conditions => { :id => 34 })

# You can use this syntax to call native storage engine functions
zoos = Zoo.all(:conditions => [ 'id = ?', 34 ])

# even mix and match
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
2
zoos = repository(:default).adapter.select('SELECT name, open FROM zoos WHERE open = 1')
#      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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
class Person
  include DataMapper::Resource
  property :id,  Serial
  property :job, String
end

Person.auto_migrate!

# Note that if you don't include the primary key, you will need to
# specify an explicit order vector, because DM will default to the
# primary key if it's not told otherwise (at least currently).
# PostgreSQL will present this rather informative error message when
# you leave out the order vector in the query below.
#
#   column "people.id" must appear in the GROUP BY clause
#   or be used in an aggregate function
#
# To not do any ordering, you would need to provide :order => nil
#
Person.all(:fields => [:job], :unique => true, :order => [:job.asc])
# ...
# 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
2
3
4
5
6
Friend.count # returns count of all friends
Friend.count(:age.gt => 18) # returns count of all friends older then 18
Friend.count(:conditions => [ 'gender = ?', 'female' ]) # returns count of all your female friends
Friend.count(:address) # returns count of all friends with an address (NULL values are not included)
Friend.count(:address, :age.gt => 18) # returns count of all friends with an address that are older then 18
Friend.count(:address, :conditions => [ 'gender = ?', 'female' ]) # returns count of all your female friends with an address

Minimum and Maximum

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

Average and Sum

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

# Get the total value of a property
Friend.sum(:age) # returns total age of all friends
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
2
Friend.aggregate(:city, :all.count) # returns the city names and the number of friends living in each city
# e.g. [['Hamburg', 3], ['New York', 4], ['Rome', 0], ... ]