Support joins with scala collections using temporary tables

[hvesalai]

Traditionally the way ORMs have supported joins over external data is the use of the SQL IN keyword, i.e.

select * from "foo" where "bar" IN ( /* list of values */ );

This is equivalent to creating a temporary table and doing an inner join on that table.

create temporary table "t1" ( "bar" text ) on commit drop;
-- insert the list of values to t1
select * from "foo" natural join "t1";

While it is true, the latter has a more complex SQL syntax, it has the following benefits:

• it has potentially better performance
• the select statement can be compiled and prepared
• it is much more versatile

As to the last point, here is something you cannot do with the IN keyword.

create temporary table "t2" ( "bar" text, "zot" number ) on commit drop;
-- insert the list of tuples to t2
select * from "foo" natural join "t2";

I.e. selecting all the rows from foo where bar = t2.bar and zot = t2.zot.

What I'm requesting is that slick support a nice DSL for doing a join with a scala collection in which the user of the API wouldn't have to handle (or even know about) the createion of the temporary table and the insertion of the values to that table.

E.g. with the current inner join syntax:

val names = List("Heikki" -> "Vesalainen", "Stefan" -> "Zeiger")
val q = persons join names on { (p, n) => p.firstName === n._1 && p.lastName === n._2 }

would result in

create temporary table "t1" ( "x1" text, "x2" text ) on commit drop;
-- insert names to t1
select * from "persons" as "p" inner join "t1" as "n" on "p"."firstName" = "n"."x1" and "p"."lastName" = "n"."x2";

[cvogt]

An alternative to this would be fetching all persons and doing the join in-memory. Which one is more efficient depends on which collections is larger, the in-memory one or the (permanent) database table. We will probably need an api for exposing this decision to the user, not only for temporary tables, but also for distributed queries joining tables from multiple dbs. An alternative could be an automatic decision based on profiling or heuristics or something like that.

[hvesalai]

I would have needed this again today.

[KLBonn]

Me too.
For now it seems I have to do it in-memory.

Side note: I know of close to zero cases where the in-memory (application input) collection would exceed the respective database table size. By far the most cases of collection comparisons (should) have been located on db side.

[mboogerd]

@cvogt, you're of course correct in that efficiency depends on the size of the scala set vs. the table size, however I agree with @KLBonn that the majority of use cases the former will be smaller. In our current project we do quite a bit of micro-batching, which would be helped tremendously if this was a slick feature!

[Pyppe]

👍

[thirdy]

Would have helped me too. Any updates on this?

[francescopellegrini]

👍

[cvogt]

maybe something @radsaggi can look at later this summer during GSOC

[alexander-myltsev]

👍

[virusdave]

👍
This is something i run into all the time as well.

[kemmar]

I also would love this feature

[tobiatesan]

Just pointing out that in absence of this feature, people are more likely to store chunks of results inside a Scala variable, which carries a performance penalty, and potentially try to use it in an IN SET clause.

Which might then involve running into #1739

[WayneWang12]

it seems a quite good feature to me.

[hvesalai]

damn I've had some great ideas in the past. 10 years ago.

[nafg]

Couldn't it be done with a VALUES expression? At least in Postgres I'm pretty sure it could. Sent with Shortwave <https://www.shortwave.com?utm_medium=email&utm_content=signature&utm_source=bmFmdG9saWd1Z0BnbWFpbC5jb20=>

…

On Mon Mar 25, 2024, 08:40 PM GMT, Heikki Vesalainen ***@***.***> wrote: damn I've had some great ideas in the past. 10 years ago. — Reply to this email directly, view it on GitHub <#799 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAYAUHDIGQFZ5X5JSZ6CRDY2CDT5AVCNFSM4APEEPT2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBRHA4DONJTGQ3Q>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>