CODEWITHSUNDEEP
scalaslickslick-3.0
Ashley Mercer
Ashley Mercer

Reputation: 2078

Scala slick: filter using "in" on multiple columns

Suppose I have the following table structure:

create table PEOPLE (
    ID integer not null primary key,
    NAME varchar(100) not null
);

create table CHILDREN (
    ID integer not null primary key,
    PARENT_ID_1 integer not null references PERSON (id),
    PARENT_ID_2 integer not null references PERSON (id)
);

and that I want to generate a list of the names of each person who is a parent. In slick I can write something like:

for {
  parent <- people
  child  <- children if {
    parent.id === child.parent_id_1 ||
    parent.id === child.parent_id_2
  }
} yield {
  parent.name
}

and this generates the expected SQL:

select p.name
from people p, children c
where p.id = c.parent_id_1 or p.id = c.parent_id_2

However, this is not optimal: the OR part of the expression can cause horrendously slow performance in some DBMSes, which end up doing full-table scans to join on p.id even though there's an index there (see for example this bug report for H2). The general problem is that the query planner can't know if it's faster to execute each side of the OR separately and join the results back together, or simply do a full table scan [2].

I'd like to generate SQL that looks something like this, which then can use the (primary key) index as expected:

select p.name
from people p, children c
where p.id in (c.parent_id_1, c.parent_id_2)

My question is: how can I do this in slick? The existing methods don't seem to offer a way:

What I'd like to be able to write is something like this:

for {
  parent <- people
  child  <- children
  if parent.id in (child.parent_id_1, child.parent_id_2)
} yield {
  p.name
}

but that's not possible right now.

[1] My actual design is a little more complex than this, but it boils down to the same problem.

[2] Some DBMSes do have this optimisation for simple cases, e.g. OR-expansion in Oracle.

Upvotes: 5

Views: 1369

Answers (1)

Ashley Mercer
Ashley Mercer

Reputation: 2078

Turns out this isn't currently (as at slick 3.2.3) possible, so I've raised an issue on github and submitted a pull request to add this functionality.

Upvotes: 0

Related Questions