Intersect inside where clause

[rrottier]

I am trying to run the following query:

(select sessions
    (join events (= :events.sessionid :sessionid))
    (fields :clinic :events.sessionid)
    (where {:sessions.istrainingmode false
            :sessions.sessionid [in 
                                 (intersect 
                                  (queries 
                                   (subselect events
                                              (fields :sessionid)
                                              (where {:name [like "%Write%"]}))
                                   (subselect events
                                              (fields :sessionid)
                                              (where {:name [like "%Impedance%"]})))
                                   (order :sessionid)))]}))

It fails because the inner query executes first and then the outer query fails on receiving the map. In these type of queries the entire query should be build first.

[immoh]

You need to wrap intersect with subselect if you use it inside where condition.

[rrottier]

I tried that:

(select sessions
    (join events (= :events.sessionid :sessionid))
    (fields :clinic :events.sessionid)
    (where {:sessions.istrainingmode false
            :sessions.sessionid [in (subselect (intersect
(queries (subselect events
                    (fields :sessionid)
                    (where {:name [like "%Maxima%"]}))
          (subselect events
                    (fields :sessionid)
                    (where {:name [like "%Impedance%"]})))))]}))

I get the following error: "org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3."

[immoh]

There seems to be a bug, Korma duplicates bind params values inside intersect leading to mismatch in count (5 instead of 3):

(dry-run (select ...))
dry run :: SELECT "sessions"."clinic", "events"."sessionid" FROM "sessions" LEFT JOIN "events" ON "events"."sessionid" = "sessions"."sessionid" WHERE ("sessions"."istrainingmode" = ? AND "sessions"."sessionid" IN ((SELECT "events"."sessionid" FROM "events" WHERE ("events"."name" LIKE ?)) INTERSECT (SELECT "events"."sessionid" FROM "events" WHERE ("events"."name" LIKE ?)))) :: [false %Maxima% %Impedance% %Maxima% %Impedance%]
=> [{:id 1}]

As a workaround, isn't your query equivalent to:

(select sessions
  (join events (= :events.sessionid :sessionid))
  (fields :clinic :events.sessionid)
  (where {:sessions.istrainingmode false
          :sessions.sessionid      [in (subselect events
                                         (fields :sessionid)
                                         (where (and (like :name "%Maxima%")
                                                     (like :name "%Impedance%"))))]}))

[rrottier]

Thanks for the response and offering a workaround. But I am afraid it is not quite equivalent. My query actually looks for sessions where different events associated with that session matches the strings where the one you propose just look in a single event which has both strings.

[immoh]

True, they're not equivalent. What was I thinking..