Get first entry from an associated table Ruby on Rails

Question

I have a one to many relationship: User has many Payments. I am trying to find a query that gets the first payment of each user(using created_at from the payments table).

I have found a similar question with an SQL response, but I have no idea how to write it with Active Record.

how do I query sql for a latest record date for each user

Quoting the answer:

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

For me, it would be min instead of max.

Thank you :)

what do you expect the result look like? All payments? or they have to come with their users as well? — lusketeer, Aug 08 '16 at 15:21

Fakhir Shad · Answer 1 · 2016-08-08T15:28:55.797

1

Try this one for POSTGRES

Payment.select("DISTINCT ON(user_id) *").order("user_id, created_at ASC")

And For SQL

Payment.group(:user_id).having('created_at = MAX(created_at)')

edited Aug 08 '16 at 15:28

answered Aug 08 '16 at 15:15

Fakhir Shad

1,071
8
20

I can't use a loop, I am trying to do everything in one query. Practically I need @user.payments.first for each user, from one query. – Ioana Surdu Bob Aug 08 '16 at 15:17
Please check the updated answer. Hope it will serve your case – Fakhir Shad Aug 08 '16 at 15:31
@FakhirShad for the SQL one, i think it should be `MIN(created_at)` – lusketeer Aug 08 '16 at 15:34
Yeah you can use min or max as per your requirement. Mix will provide you the first ever payment made and max will retrieve last payment – Fakhir Shad Aug 10 '16 at 04:57

score 0 · Answer 2 · answered Aug 09 '16 at 05:38

If I'm going to answer the question above with: (I don't based on given raw SQL)

User has many Payments. I am trying to find a query that gets the first payment of each user(using created_at from the payments table).

Let say:
# Assumed to have a Single User, as reference
user = User.first

# Now, get first payment (from Payment model)
user.payments.last
# .last since it will always get the first created row by created_at.

If I fully understand what you're trying to do. I'm don't know why you need max or min date?

kamal · Answer 3 · 2016-08-09T12:09:08.700

What about this?

If you want first payment of each user

dates = Payment.group(:user_id).minimum(:created_at).values
payments = Payment.where(created_at: dates)

From payment you can find user too.

I think you have username as foreign key, you can change accordingly. :) Let me know if you face any issue, as I tested it works.

I know this answer is not the best, but it will work even or transactions with milliseconds difference, as rails saves date(created_at and updated_at) with ms level.

score 0 · Accepted Answer · answered Aug 10 '16 at 08:31

I am sorry for not replying to everything, but after multiple tests, this is the quickest answer (in run time) I came with:

Payment.where(:id => Payment.group(:user_id).pluck(:id))

I am saying it might not be the quickest way because I am using a sub query. I am getting the unique values and getting the ID's:

Payment.group(:user_id).pluck(:id)

Then I am matching those ID's.

The downside of this is that it won't work reversed, for getting the last payment.

There was also a possibility to use group_by and map but, since map is coming from ruby, it is taking much more time.

score -1 · Answer 5 · answered Aug 08 '16 at 15:23

-1

I'm not sure but try this :

In your controller :

  def Page
    @payments = Payment.first
  end

in your html.erb :

<% @payments.each do |payment| %>
  <p> <%= payment.amount %> </p>

Hope this help !

answered Aug 08 '16 at 15:23

Florian_CEO

31
7

I am looking for a query, not a loop, as I said in the other question :) – Ioana Surdu Bob Aug 08 '16 at 15:26

score -1 · Answer 6 · answered Aug 08 '16 at 18:25

-1

Record.association.order(:created_at).first

answered Aug 08 '16 at 18:25

Lucas Ferronato

119
1
9

Get first entry from an associated table Ruby on Rails

6 Answers6