[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]
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
String_agg returns a string containging the values returned, separated by a delimiter. In the following example, we are searching for the name of all the regions in the regions table and we want it all returned as a single string with a delimiting comma.
(query (:select (:string_agg 'name ",") :from 'regions)) (("Central America,Middle East,North America,South America,Central Asia,Pacific,Caribbean,Eastern Europe,Western Europe,EMEA,APAC,LATAM,Emerging,US,Canada,Africa,All,Asia,Eastern Africa,Middle Africa,Northern Africa,Southern Africa,Western Africa,Oceania,Northern Europe,Southern Europe,Eastern Asia,South Central Asia,South East Asia"))Simple example for a sum:
(query (:select (:sum 'population) :from 'countries) :single) 14427958899