Datamapper: Sorting results through association

Question

I'm working on a Rails 3.2 app that uses Datamapper as its ORM. I'm looking for a way to sort a result set by an attribute of the associated model. Specifically I have the following models:

class Vehicle
  include DataMapper::Resource

  belongs_to :user
end

class User
  include DataMapper::Resource

  has n, :vehicles
end

Now I want to be able to query the vehicles and sort them by the name of the driver. I tried the following but neither seems to work with Datamapper:

> Vehicle.all( :order => 'users.name' )
ArgumentError: +options[:order]+ entry "users.name" does not map to a property in Vehicle

> Vehicle.all( :order => { :users => 'name' } )
ArgumentError: +options[:order]+ entry [:users, "name"] of an unsupported object Array

Right now I'm using Ruby to sort the result set post-query but obviously that's not helping performance any, also it stops me from further chaining on other scopes.

score 7 · Accepted Answer · answered Sep 27 '12 at 14:09

7

I spent some more time digging around and finally turned up an old blog which has a solution to this problem. It involves manually building the ordering query in DataMapper.

From: http://rhnh.net/2010/12/01/ordering-by-a-field-in-a-join-model-with-datamapper

def self.ordered_by_vehicle_name direction = :asc
  order = DataMapper::Query::Direction.new(vehicle.name, direction)
  query = all.query
  query.instance_variable_set("@order", [order])
  query.instance_variable_set("@links", [relationships['vehicle'].inverse])
  all(query)
end

This will let you order by association and still chain on other scopes, e.g.:

User.ordered_by_vehicle_name(:desc).all( :name => 'foo' )

It's a bit hacky but it does what I wanted it to do at least ;)

answered Sep 27 '12 at 14:09

Peter Duijnstee

3,679
2
17
30

1

Awesome, glad you found what you needed! – Baylor Rae' Sep 27 '12 at 18:17
1

Yeah saves a lot of headache this way. Hopefully it will be useful to someone else some time. But thanks for all the time you put in still :) – Peter Duijnstee Sep 28 '12 at 11:33
This is a related link, lots of nice answers on the site, and this thread references the link you mentioned as well: http://www.mail-archive.com/datamapper@googlegroups.com/msg04399.html – Aram Kocharyan Aug 06 '13 at 12:22

score 1 · Answer 2 · answered Sep 23 '12 at 14:53

1

Note: I'm not familiar with DataMapper and my answer might not be within the standards and recommendations of using DataMapper, but it should hopefully give you the result you're looking for.

I've been looking through various Google searches and the DataMapper documentation and I haven't found a way to "order by assocation attribute". The only solution I have thought of is "raw" SQL.

The query would look like this.

SELECT vehicles.* FROM vehicles
LEFT JOIN users ON vehicles.user_id = users.id
ORDER BY users.name

Unfortunately, from my understanding, when you directly query the database you won't get the Vehicle object, but the data from the database.

From the documentation: http://datamapper.org/docs/find.html. It's near the bottom titled "Talking directly to your data-store"

Note that this will not return Zoo objects, rather the raw data straight from the database

answered Sep 23 '12 at 14:53

Baylor Rae'

3,970
18
39

Thanks for the effort, in the mean time I've been doing more searching myself and came to the same conclusion. It sucks because the rest of the query is quite complex. Right now I have a fairly elegant DM statement but it doesn't sort, so looks like I'm gonna have the build the whole thing by hand. In any case I think this is the best answer there is, thanks again! – Peter Duijnstee Sep 23 '12 at 19:13
1

@PeterDuijnstee it's a shame there aren't more people here to help with your problem. I can't imagine an ORM not allowing you order your results by an association. If I find something in the future I'll try and remember to let you know. – Baylor Rae' Sep 23 '12 at 19:35
If you do you'd have my eternal gratitude ;) I was also very surprised but haven't been able to find anything. – Peter Duijnstee Sep 23 '12 at 20:21

score -3 · Answer 3 · answered Sep 14 '12 at 17:50

-3

Vehicle.joins(:user).order('users.name').all

or in Rails 2.3,

Vehicle.all(:joins => "inner join users on vehicles.user_id = user.id", :order => 'users.name')

answered Sep 14 '12 at 17:50

Marlin Pierce

9,073
2
24
45

Unfortunately those are both ActiveRecord examples that won't work with Datamapper ;) But thanks for trying! – Peter Duijnstee Sep 14 '12 at 18:05

Datamapper: Sorting results through association

3 Answers3

Linked