How to fetch parents based on their child's records
How to fetch parents based on their child's records
I have two models like
company.rb
class Company < ApplicationRecord
has_many :posts
end
post.rb
class Post < ApplicationRecord
belongs_to :company
scope :notClosed, -> {where(closed: false)}
scope :published, -> {where(published: true)}
end
I want to fetch companies which carries at least one post with matching post scope
scope
Currently my queries is
Company.where(company_type: "Private").all
It's return all companies but how to modify this query for my needs.
I don't want to fetch company which post record zero based on post
scope like notClosed and published @SebastianPalma– jesica
Jul 2 at 3:58
scope
notClosed
published
So, try
Company.joins(:posts).distinct.merge(Post.notClosed).– Sebastian Palma
Jul 2 at 4:00
Company.joins(:posts).distinct.merge(Post.notClosed)
Hey @SebastianPalma, maybe it's working see the query
SELECT DISTINCT "companies".* FROM "companies" INNER JOIN "posts" ON "posts"."company_id" = "companies"."id" WHERE "companies"."company_type" = ? AND "posts"."closed" = ? AND "posts"."published" = ? LIMIT ? [["company_type", "Private"], ["closed", "f"], ["published", "t"], ["LIMIT", 11]], can you please suggest me about speed performance issue because my database is only posts over one million, I think you can post this as a answer.– jesica
Jul 2 at 4:13
SELECT DISTINCT "companies".* FROM "companies" INNER JOIN "posts" ON "posts"."company_id" = "companies"."id" WHERE "companies"."company_type" = ? AND "posts"."closed" = ? AND "posts"."published" = ? LIMIT ? [["company_type", "Private"], ["closed", "f"], ["published", "t"], ["LIMIT", 11]]
3 Answers
3
published_post_companies = Company.joins(:posts).distinct.merge(Post.published)
notClosed_post_companies = Company.joins(:posts).distinct.merge(Post.notClosed)
This will get you all Companies (once) with at least one post that's "not closed":
Company.includes(:posts).joins(:posts).where(company_type: "Private").merge(Post.notClosed)
It's execution time is to long
– jesica
Jul 2 at 4:17
Do you have database indices on all the relevant columns, i. e. company.company_type, post,company_id, post.closed, post.published?
– MattW.
Jul 2 at 8:10
The explicit Rails way: get the IDs of companies from the Post scope then query on those IDs:
Post
Company.where(id: Post.published.pluck(:company_id).uniq)
Note, however, this might be expensive if you have a huge amount of posts. There could be a more efficient way using SQL.
My database is only posts over one million
– jesica
Jul 2 at 4:01
where(id: Post.published.select(:company_id)) would serve you better, that would do a subquery inside the database rather than pulling a bunch of data out of the database and then sending it back. It also neatly avoids duplicates that JOINs can introduce.– mu is too short
Jul 2 at 4:02
where(id: Post.published.select(:company_id))
Hello Sir @muistooshort, I have several condition like
type: "private", notClosed, published & not expired how the query based on that condition, can you please suggest me.– jesica
Jul 2 at 4:10
type: "private"
notClosed
published
expired
@muistooshort TIL, ty for the comment.
– Tamer Shlash
Jul 2 at 4:12
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
I want to fetch companies which carries at least one post, actually, an INNER JOIN keyword would return the companies with posts associated, you can append a distinct statement to get non-repeated records. Can you explain further what you're trying to do?
– Sebastian Palma
Jul 2 at 3:55