Subqueries with select function in CLSQL

Question

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?

Answer 1

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.