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

What is the issue with two queries? Is there a more complicated example you are trying to optimize that we aren't seeing? — John Naegle, Nov 29 '12 at 14:30
No, I just want to execute the SQL in my question with an activerecord query. — Michiel de Mare, Nov 29 '12 at 14:43

John Naegle · Answer 1 · 2012-11-29T15:46:43.010

22

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])

edited Nov 29 '12 at 15:46

answered Nov 29 '12 at 14:34

John Naegle

8,077
3
38
47

`Association named 'pages_paragraphs' was not found` – Michiel de Mare Nov 29 '12 at 14:44
1

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) – John Naegle Nov 29 '12 at 15:43

score 2 · Answer 2 · answered Apr 13 '17 at 06:19

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

answered Apr 13 '17 at 06:19

Toby 1 Kenobi

4,717
2
29
43

score 1 · Answer 3 · answered Nov 29 '12 at 11:57

1

You can use includes for this :

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

answered Nov 29 '12 at 11:57

Adrien Coquio

4,870
2
24
37

`includes` uses an extra query to load related data. The OP asked for the whole thing in one query, so this doesn't work. – Toby 1 Kenobi Jun 16 '21 at 04:48

Efficient ActiveRecord has_and_belongs_to_many query

3 Answers3

Linked