I'm attempting to create a subquery with the clsql:select function:
CL-USER> (select [books.bookid]
:from [books]
:where
(sql-in [books.bookid]
(select [bookid]
:from [bookauthors]
:where
(sql-= [bookauthors.authorid] 120))))
;; 2015-03-07T06:37:08 /books/ => SELECT BOOKID FROM BOOKAUTHORS WHERE (BOOKAUTHORS.AUTHORID = 120)
;; 2015-03-07T06:37:08 /books/ => SELECT BOOKS.BOOKID FROM BOOKS WHERE (BOOKS.BOOKID IN ((157)))
((157))
("bookid")
It works, but instead of generating one query with a sub-select clause, clsql runs two queries. This isn't going to be as efficient as letting the postgresql backend handle the whole thing.
CL-USER> (clsql-sys:db-type-has-subqueries? :postgresql)
T
Evidently the postgresql connector supports subqueries. Is there a way to get the select function to generate them?
In your above calls you are actually running the inner select, then splicing the results into the outer call.
You should use sql-expressions instead of functions. If you (clsql-sys:file-enable-sql-reader-syntax) this can be accomplished with square brackets as follows.
(select [books.bookid]
:from [books]
:where
[in [books.bookid]
[select [bookid]
:from [bookauthors]
:where
[= [bookauthors.authorid] 120]]))
Also, you might wish to use the :postgresql-socket3 backend as it is the most robust / most recent of the three postgresql clsql backends (it uses the cl-postgresql library provided by postmodern to access postgresql through version 3 of its socket api. :posgresql-socket uses version 2 of the postgres socket api, and :postgres uses the FFI through the C client.