0

When I do includes it left joins the table I want to filter on, but when I add pluck that join disappears. Is there any way to mix pluck and left join without manually typing the sql for 'left join'

Here's my case:

Select u.id 
From users u
Left join profiles p on u.id=p.id
Left join admin_profiles a on u.id=a.uid
Where 2 in (p.prop, a.prop, u.prop)

Doing this is just loading all the values:

Users.includes(:AdminProfiles, :Profiles).where(...).map{ |a| a[:id] }

But when I do pluck instead of map, it doesn't left join the profile tables.

Mohammad AbuShady
  • 40,884
  • 11
  • 78
  • 89
user433342
  • 859
  • 1
  • 7
  • 26

1 Answers1

2

Your problem is that you're using includes which doesn't really do a join, instead it fires a second query after the first one to query for the associations, in your case you want them both to be actually joined, so for that replace includes(:something) with joins(:something) and every thing should work fine.

Replying to your comment, i'm gonna quote few parts from the rails guide about active record query interface

  1. From the section Solution to N + 1 queries problem 

    clients = Client.includes(:address).limit(10)
clients.each do |client|
  puts client.address.postcode
end

    The above code will execute just 2 queries, as opposed to 11 queries in the previous case:

    SELECT * FROM clients LIMIT 10
SELECT addresses.* FROM addresses WHERE (addresses.client_id IN (1,2,3,4,5,6,7,8,9,10))

    as you can see, two queries, no joins at all.

  2. From the section Specifying Conditions on Eager Loaded Associations link

    Even though Active Record lets you specify conditions on the eager loaded associations just like joins, the recommended way is to use joins instead.

    Then an example:

    Article.includes(:comments).where(comments: { visible: true })

    This would generate a query which contains a LEFT OUTER JOIN whereas the joins method would generate one using the INNER JOIN function instead.

    SELECT "articles"."id" AS t0_r0, ... "comments"."updated_at" AS t1_r5 FROM "articles" LEFT OUTER JOIN "comments" ON "comments"."article_id" = "articles"."id" WHERE (comments.visible = 1)

    If there was no where condition, this would generate the normal set of two queries.

Mohammad AbuShady
  • 40,884
  • 11
  • 78
  • 89
  • Includes does a left join, it doesn't fire another query. Join does a inner join, in order to make it do a left join you need to write manual sql. My question is fine. – user433342 Feb 21 '15 at 12:26
  • Please see my amended question to understand why I require left joins – user433342 Feb 22 '15 at 09:25
  • "Includes does a left join, it doesn't fire another query" -> `includes` tries to be smart and does either one depending on some logic. – Lloeki Nov 02 '16 at 15:14