CODEWITHSUNDEEP
ruby-on-railsactiverecord
p.matsinopoulos
p.matsinopoulos

Reputation: 7810

In 1-to-many Rails ActiveRecord relation, what is the best practice to get parent records that they only have children records?

I have a Post model that has many comments

class Post < ApplicationRecord
  has_many :comments
end

How do I get distinct posts that have comments excluding the posts that do not have comments?

I believe that Post.joins(:comments).distinct("posts.*") does the job.

Is there any better alternative?

I emphasize that the result should not have duplicate entries of posts, which is what happens when we join with comments (one-to-many relationship).

Upvotes: 1

Views: 805

Answers (4)

Prabakaran
Prabakaran

Reputation: 353

You don't have to really use distinct at the end, it's not at all effective as you are selecting everything by default (posts.*) which has a unique primary key (id) for each record.

Post.joins(:comments)
# SELECT `posts`.* FROM `posts` INNER JOIN `comments` ON `comments`.`post_id` = `posts`.`id`

Note:

To see the difference yourself please try to run below queries.

Post.joins(:comments).count
Post.joins(:comments).distinct.count

# Both should be giving you the same numbers.

Upvotes: 1

Lalu
Lalu

Reputation: 752

You can also achieve this using subquery:

Post.where(id: Comment.select(:post_id).distinct)

Load time depends on the number of comments in the database.

Upvotes: 0

ReggieB
ReggieB

Reputation: 8247

Looking at this again, and considering Mark's comment to my original solution, the simplest solution may just be to drop the "posts.*" from your original.

Post.joins(:comments).distinct

Upvotes: 3

ReggieB
ReggieB

Reputation: 8247

This works, though obviously a matter of taste as to whether it is better:

Post.left_outer_joins(:comments).where.not(comments: {id: nil})

And left_outer_joins came in with Rails 5, so with earlier versions a more verbose solution is needed. See Finding nil has_one associations in where query

The nicest thing about it, is it pairs nicely with the opposite query:

Post.left_outer_joins(:comments).where(comments: {id: nil})

Which is all the posts without comments

Upvotes: 1

Related Questions