postmodern-s (Set)

[Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]

Values in a set

Often I need to get a list of results where a query is in a particular set. The following would be the syntax in postmodern sql where the set is a list. If you want to use a vector, then you need to use Any:

The following are equivalent

(query (:select 'name :from 'countries :where (:in 'id (:set 20 21 23)))) (let ((x (list 20 21 23))) (query (:select 'name :from 'countries :where (:in 'id (:set x))))) (query (:select 'name :from 'countries :where (:in 'id (:set (list 20 21 23))))) (("Greece") ("US") ("UK"))

However, the following will generate an error about inability to convert to an sql literal

(query (:select 'name :from 'countries :where (:in 'id (:set '(20 21 23)))))

Now with selecting a dao

(select-dao 'countries (:in 'id (:set (list 20 21 23 (#<COUNTRIES {1002B8C111}> #<COUNTRIES {1002B8C801}> #<COUNTRIES{1002B8D611}>)))))

Now with selecting from a vector. Note both the use of any* and := instead of :in.

(let ((x (vector 20 21 23))) (query (:select 'name :from 'countries :where (:= 'id (:any* x))))) (("Greece") ("US") ("UK"))

Note that the responses are still coming back in a list of lists

If you are trying to use a list in a parametized statement, you can't. You have to convert the list to a vector and use "any" rather than "in." See example in Parametized Statements

Comments