how do you find the latest row for each user in rails?

Go To StackoverFlow.com

2

Whats the Rails way to do a query for the latest lesson_plan record for each user_id?

class LessonPlan < ActiveRecord::Base
    belongs_to :user
end

All the posts I've seen include joins or subqueries. Isn't there a simple way to do this?

I've tried

LessonPlan.select(['distinct(user_id)', :id, :created_at]).where(:user_id => users).order('created_at DESC') 

Which produces a query like.

SELECT distinct(user_id), id, created_at FROM `lesson_plans` WHERE `lesson_plans`.`user_id` IN (110031, 110032, 110033, 110034) ORDER BY created_at DESC

But when I try this query, it returns rows with duplicate user_id's.

Sorry if this is a duplicate question, I've really looked around for a solution.

2012-04-04 00:53
by Tim
Which database - Harish Shetty 2012-04-04 02:28
we are using mysq - Tim 2012-04-04 03:52


3

Rails#ActiveRecord:

LessonPlan.select([:user_id, :id, 'MAX(created_at)']).group(:user_id).limit(10)

MySql :

select id, user_id, MAX(created_at) from lesson_plans group by user_id limit 10
2012-04-04 07:54
by Vik


0

May be its work

LessonPlan.select([:user_id, :id, 'MAX(created_at)']).group(:user_id)
2012-04-04 07:35
by Hardik Patel
correct your query it will give syntax error - Vik 2012-04-04 07:58
OOPs, sorry, that can be resolve himself, anyway, I just edite - Hardik Patel 2012-04-04 08:58
Ads