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 > 1 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
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.max(: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], ... ]