Rails nested joins with third Model

Question

I have 3 models.

User.rb

has_many :user_cards

UserCard.rb

belongs_to :CardGroup

I want to get all users, with cards and the CardGroup information in one single query.

 User.joins("LEFT OUTER JOIN user_cards ON user_cards.user_id = users.id AND user_cards.created_at BETWEEN '#{@start_time.to_s(:db)}' AND '#{@end_time.to_s(:db)}'")
 .group('users.id, users.mobile, user_cards.user_id, user_cards.bin)
         .select(
      'users.id AS uid, users.name AS uname, users.mobile AS umobile,' \
      'count(distinct(user_cards.id)) AS total_cards, \
      'user_cards.bin AS card_bins'
    )

Now, I want to join the third model CardGroup as well and get user_card.card_group.name in the above query.

How can I do it with nested join / any other way?

What Rails version are you using?

Sebastián Palma
– Sebastián Palma

2020-05-25 09:30:48 +00:00
Commented May 25, 2020 at 9:30 — Sebastián Palma
– Sebastián Palma, Commented May 25, 2020 at 9:30
We use Rails 5.2

Suganya Selvarajan
– Suganya Selvarajan

2020-05-25 09:34:20 +00:00
Commented May 25, 2020 at 9:34 — Suganya Selvarajan
– Suganya Selvarajan, Commented May 25, 2020 at 9:34

Matthew Hinea · Accepted Answer · 2020-06-02 22:50:33Z

1

First, you should use snake case in ruby.

belongs_to :card_group

Second, this part of your join statement would be better off in a where clause.

AND user_cards.created_at BETWEEN '#{@start_time.to_s(:db)}' AND '#{@end_time.to_s(:db)}'"

Third, the join query itself is more readable in Ruby. That's why you put the relation on the model in the first place. So replace:

LEFT OUTER JOIN user_cards ON user_cards.user_id = users.id

with

User.left_outer_joins(:user_cards)

Now adding a 3rd model joined through the 2nd is as easy as:

User.left_outer_joins(user_cards: :card_group)

edited Jun 2, 2020 at 22:50

answered May 26, 2020 at 1:20

Matthew Hinea

1,95121 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails nested joins with third Model

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related