Efficient ActiveRecord has_and_belongs_to_many query

Question

I have Page and Paragraph models with a has_and_belongs_to_many relation. Given a paragraph_id, I'd like to get all matching pages. e.g.:

pages = Paragraph.find(paragraph_id).pages.all

However, this takes two queries. It can be done in one query:

SELECT "pages".* FROM "pages" 
INNER JOIN "pages_paragraphs" ON "pages_paragraphs"."page_id" = "pages"."id" 
WHERE "pages_paragraphs"."paragraph_id" = 123

But can this be done without

• using find_by_sql
• without modifications to the page_paragraphs table (e.g. adding an id).

Update:

My page model looks like this:

class Page < ActiveRecord::Base
  has_and_belongs_to_many :paragraphs, uniq: true
end

Numinous

@jdoe I've clarified my question, thanks for your efforts. – Numinous

What is the issue with two queries? Is there a more complicated example you are trying to optimize that we aren't seeing? – Separation

No, I just want to execute the SQL in my question with an activerecord query. – Numinous

Answer 1

With a has_many :through relationship, you could use this:

pages = Page.joins(:pages_paragraphs).where(:pages_paragraphs => {:paragraph_id => 1})

Take a look at Specifying Conditions on the Joined Tables here: http://guides.rubyonrails.org/active_record_querying.html

If you want the pages and paragraphs together:

pages = Page.joins(:pages_paragraphs => :paragraph).includes(:pages_paragraphs => :paragraph).where(:pages_paragraphs => {:paragraph_id => 1})

With a has_and_belongs_to_many:

pages = Page.joins("join pages_paragraphs on pages.id = pages_paragraphs.page_id").where(["pages_paragraphs.paragraph_id = ?", paragraph_id])

Separation

Association named 'pages_paragraphs' was not found – Numinous

Without the a model/association for your pages_paragraphs table, you can do the join conditions as strings. Page.joins("join pages_paragraphs on pages_paragraphs.page_id = pages.id").where("pages_paragraphs.paragraph_id = ?", paragraph_id) – Separation

Answer 2

You can use eager_load for this

pages = Paragraph.eager_load(:pages).find(paragraph_id).pages

I found an article all about this sort of thing: 3 ways to do eager loading (preloading) in Rails 3 & 4 by Robert Pankowecki

Chyme

Answer 3

You can use includes for this :

pages = Paragraph.includes(:pages).find(paragraph_id).pages

Susannesusceptibility

includes uses an extra query to load related data. The OP asked for the whole thing in one query, so this doesn't work. – Chyme