Interesting, so it seems postgres is smart enough to interpret it the same
way. This was very helpful.
I don't mind using this way... I was just worried about performance
implications.
Thanks for your response and analysis!
On Wednesday, June 17, 2020 at 7:45:10 PM UTC+3, Jeremy Evans wrote:
>
> 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/0614fd62-14cc-4035-ab1e-bade3eb06241o%40googlegroups.com.
