0

What I want to do feels pretty basic to me, but I'm not finding a way to do it using DataMapper without resorting to raw SQL. That would look something like:

select u.id, u.name, count(p.id) as post_count
from posts p
inner join users u on p.user_id = u.id
group by p.user_id
order by post_count desc;

The intention of the above query is to show me all users sorted by how many posts each user has. The closest I've found using DataMapper is aggregate, which doesn't give me back resource objects. What I'd like is some way to generate one query and get back standard DM objects back.

Dan Tao
  • 125,917
  • 54
  • 300
  • 447

1 Answers1

-1

Assuming you have relationships 

has_n, :posts

you should be able to do

User.get(id).posts.count

or

User.first(:some_id => id).posts.count

or

u = User.get(1)
u.posts.count

you can also chain conditions

User.get(1).posts.all(:date.gt => '2012-10-01')

see scopes and chaining here http://datamapper.org/docs/find.html

finally add the ordering

User.get(1).posts.all(:order => [:date.desc])
socialsiem
  • 54
  • 5
  • I appreciate the answer; but unless I'm missing something, this doesn't tell me how to *sort* by child record *count*, does it? That is fundamentally my question—i.e., based on your examples, how would I get *all users* sorted by *posts per user*? (Presumably something like `User.all(:order => { :posts => :count })` or something like that, though that obviously doesn't work.) – Dan Tao Oct 31 '12 at 16:49
  • That works if you are tracking post count in the user object which is fairly trivial to do. User.all(:order => {:post_count.desc}). This may be easier on your database overall, you just need to increment when you add a post. – socialsiem Oct 31 '12 at 18:02
  • I agree that it would be trivial and that it may be easier on the database. However, I don't *think* (with proper indexing) it should really have a significant impact on performance; and more importantly, I'd rather not be forced into a specific database design simply to accommodate a particular ORM! Doesn't that seems a little backwards? – Dan Tao Oct 31 '12 at 18:19
  • you might be able to do User.get(1).posts.all(:fields =>[:user_id]) this would require you to embed the user id in the post model vs using a resource table. :fields triggers group_by – socialsiem Oct 31 '12 at 21:50