Slick function for SQL exists

Question

I want to construct an SQL along this but try not to use sqlu.

 select el.oid, el.name, el.res_cat from el
    left join bk on (el.cat = bk.cat and bk.oid=100)
 where not exists (select 1 from dates bd where
     el.oid=bd.lots_oid and bd.bk_oid = bk.oid) and el.e_oid=bk.e_oid

Are there Slick functions for SQL exists or not exists? Thanks

Update 1

I realized my mistakes when I revisited my Slick code again. I want to apologize for the false alarm I set. This is not an answer and hopefully someone can help me to rectify my mistakes. For the time being, I am using Slick's plain SQL to continue my work.

The Slick query I constructed didn't work. It was close to the SQL I wanted. What I did was,

val elQuery = elTable.joinLeft(bkTable)
  .on((el, bk) => el.cat === bk.cat && bk.oid === 100)

val query = for {
  a <- elQuery if bdTable.filterNot(bd => a._2.map(_.oid === bd.bkOid).isDefined && a._1.oid ===  bd.elOid).exists
} yield a

finalQuery.result.statements.foreach(x => Logger.debug(s"xx => $x"))

I notice filterNot does not generate a SQL not exists. This is the other portion that lost me.

What have you tried? What leads you to think Slick does not support them? And http://stackoverflow.com/questions/18864351/scalatra-slick-and-insert-if-not-exists and https://groups.google.com/forum/#!topic/scalaquery/Ai8gzwMsKeg — The Archetypal Paul, Apr 13 '16 at 08:19
there's a inSet operator in Slick. not sure if that's what you want though — pedrorijo91, Apr 13 '16 at 08:29
@TheArchetypalPaul I have tried `exists` but I am not really sure if I used it correctly. I will try again after reading the links you have provided. — thlim, Apr 13 '16 at 11:18
I can construct the SQL I wanted following the links suggested by @TheArchetypalPaul. The official document shows `exists` used in the simplest way and it didn't occur to me that `SQL exists` is generated in a for-comprehension filter if I didn't see the other examples. — thlim, Apr 18 '16 at 10:54
Excellent. Could you provide an answer to your own question for future readers? — The Archetypal Paul, Apr 18 '16 at 12:40

score 0 · Answer 1 · answered Apr 13 '16 at 05:35

I don't have enough reputation to make comment yet. But I assume that you want to get all rows that doesn't exit in dates table. I would rewrite your query like below:

SELECT
    el.oid, el.name, el.res_cat.cat
FROM
    el
    LEFT JOIN bk ON bk.cat = el.cat
        AND bk.e_oid = el.e_oid
        AND bk.oid = 100
    LEFT JOIN dates bd ON bd.lots_oid = el.oid
        AND bd.bk_oid = bk.oid
WHERE
    bd.lots_oid IS NULL

Explanation: Instead of taking NOT EXISTS, you can achieve the same thing by LEFT JOIN dates and specify on WHERE condition that the primary key (PK) for dates IS NULL. I don't know the PK for dates table, so I just add the column I know. You should adjust it to the PK of dates table.

LEFT JOINing and WHERE PK IS NULL ensures you that the row doesn't exist on the left joined table.

This doesn't actually answer the question, although it may help the OP get the data they want. It's a question about Slick, not about how to rewrite a query. — The Archetypal Paul, Apr 13 '16 at 08:17
True, it is not what I want but I am fine if it solves my problem without using `SQL exists`. The issue arises because I am convinced that `SQL exists` will solve my problem. I could be wrong though. Btw, the ` bk.e_oid = el.e_oid` inside the left join does not produce the right result. I think it should be put out of the left join. I yet to try them out. — thlim, Apr 13 '16 at 11:23

Slick function for SQL exists

1 Answers1