On Wednesday, June 17, 2020 at 5:58:09 AM UTC-7, Aryk Grosz wrote:
>
> Last sentence should be:
>
> Any reason why there isn't a way to stick on additional conditions on
> association_join so it gets put on the join condition?
>
> On Wednesday, June 17, 2020 at 3:57:10 PM UTC+3, Aryk Grosz wrote:
>>
>> I've noticed the library promotes the usage of
>>
>> dataset.association_join(association: proc{|ds| ds.where(user_id: 1)})
>>
>> From what I understand, there is no way, to "on the fly" add the
>> "user_id: 1" as a condition, am I right?
>>
>> Doing as a subquery creates a temp table in memory which from what I've
>> read loses it's ability to use indexes.
>>
>
I think older versions of MySQL took such an approach, but in general this
is not true. Here's an example from one of my apps:
puts Game.association_join(:songs=>proc{|ds|
ds.where(:id=>1..10)}).tap{|ds| puts ds.sql}.analyze
SELECT * FROM "games" INNER JOIN (SELECT * FROM "songs" WHERE (("id" >= 1)
AND ("id" <= 10))) AS "songs" ON ("songs"."gameid" = "games"."id")
Hash Join (cost=2.43..11.09 rows=9 width=109) (actual time=0.093..0.108
rows=6 loops=1)
Hash Cond: (songs.gameid = games.id)
-> Index Scan using songs_pkey on songs (cost=0.28..8.92 rows=10
width=37) (actual time=0.011..0.019 rows=10 loops=1)
Index Cond: ((id >= 1) AND (id <= 10))
-> Hash (cost=1.51..1.51 rows=51 width=72) (actual time=0.065..0.065
rows=51 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on games (cost=0.00..1.51 rows=51 width=72) (actual
time=0.010..0.029 rows=51 loops=1)
Planning Time: 0.342 ms
Execution Time: 0.156 ms
puts Game.join(:songs, :gameid=>:id, :id=>(1..10)).tap{|ds| puts
ds.sql}.analyze
SELECT * FROM "games" INNER JOIN "songs" ON (("songs"."gameid" =
"games"."id") AND ("songs"."id" >= 1) AND ("songs"."id" <= 10))
Hash Join (cost=2.43..11.09 rows=9 width=109) (actual time=0.089..0.105
rows=6 loops=1)
Hash Cond: (songs.gameid = games.id)
-> Index Scan using songs_pkey on songs (cost=0.28..8.92 rows=10
width=37) (actual time=0.010..0.018 rows=10 loops=1)
Index Cond: ((id >= 1) AND (id <= 10))
-> Hash (cost=1.51..1.51 rows=51 width=72) (actual time=0.062..0.063
rows=51 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on games (cost=0.00..1.51 rows=51 width=72) (actual
time=0.008..0.027 rows=51 loops=1)
Planning Time: 0.316 ms
Execution Time: 0.151 ms
You can see the exact same execution plan is used for both cases (subquery
vs join conditions).
> Any reason why there isn't a way to stick on addition conditions on
>> association_join so it gets put on the join table?
>>
>
Nobody's asked for it, and in general if you want custom JOIN conditions
added on a per-call basis, you may be better off using one of the normal
join methods (join/left_join/etc.) that allows you full control. Most
cases where users want custom JOIN conditions, they don't need them on a
per-call basis, but for all usage of the association, in which case they
can be handled via options on the association itself (e.g. :conditions).
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/4abf93c7-3d0d-4d89-8531-03886855b43do%40googlegroups.com.
