[postmodern-devel] Retrieving completely populated DAOs based on SQL string query
I'd like to retrieve fully populated DAOs based on a SQL string query - preferably without naming all the columns. Using query-dao, I can get DAOs which have the columns I enumerate populated. For example, the following will retrieve DAOs with just the id slot populated. (query-dao 'foo-db-name "SELECT DISTINCT foo.id FROM foo, etc.") Alternatively, I can use select-dao to return complete DAOs, but I don't know how to write the test in the form of a SQL string to limit the DAOs that will be returned. So it would help me to know how to write a SQL string which can be used as a test for select-dao. Or a way to have query-dao retrieve fully populated DAOs. Thanks for any help. Mitch
Here's one way I found to do this: (select-dao 'foo-db-name (:in 'foo.id (:set (flatten (query "SELECT DISTINCT foo.id FROM foo, etc."))))) On 10/4/2010 11:39 AM, Mitch Berkson wrote:
I'd like to retrieve fully populated DAOs based on a SQL string query - preferably without naming all the columns.
Using query-dao, I can get DAOs which have the columns I enumerate populated. For example, the following will retrieve DAOs with just the id slot populated.
(query-dao 'foo-db-name "SELECT DISTINCT foo.id FROM foo, etc.")
Alternatively, I can use select-dao to return complete DAOs, but I don't know how to write the test in the form of a SQL string to limit the DAOs that will be returned.
So it would help me to know how to write a SQL string which can be used as a test for select-dao. Or a way to have query-dao retrieve fully populated DAOs. Thanks for any help.
Mitch
_______________________________________________ postmodern-devel mailing list postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
Hi Mitch,
(select-dao 'foo-db-name (:in 'foo.id (:set (flatten (query "SELECT DISTINCT foo.id FROM foo, etc.")))))
I'm not quite sure what you are trying to achieve. Unless the 'etc' was relevant, you seem to be selecting all ids from your foo table, and then select only those rows in the foo table that have an id that exists in the foo table -- i.e., every single row. Why? If your 'etc' is a WHERE clause, you should be able to directly pass it as the test argument to select-dao. Best, Marijn
Marijn, Sorry to hide too much in the etc. It is relevant. Substituting for the etc: (select-dao 'foo-db-name (:in 'foo.id (:set (flatten (query "SELECT DISTINCT foo.id FROM foo_member INNER JOIN foo_show ON (foo_member.id = foo_show.member_id) INNER JOIN foo_series ON (foo_show.series_id = foo_series.id) WHERE foo_series.year = 2011)))) This query is something django generates from a much more civilized-looking expression. Mitch On 10/5/2010 5:02 AM, Marijn Haverbeke wrote:
Hi Mitch,
(select-dao 'foo-db-name (:in 'foo.id (:set (flatten (query "SELECT DISTINCT foo.id FROM foo, etc."))))) I'm not quite sure what you are trying to achieve. Unless the 'etc' was relevant, you seem to be selecting all ids from your foo table, and then select only those rows in the foo table that have an id that exists in the foo table -- i.e., every single row. Why? If your 'etc' is a WHERE clause, you should be able to directly pass it as the test argument to select-dao.
Best, Marijn
_______________________________________________ postmodern-devel mailing list postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
(select-dao 'foo-db-name (:in 'foo.id (:set (flatten (query "SELECT DISTINCT foo.id FROM foo_member INNER JOIN foo_show ON (foo_member.id = foo_show.member_id) INNER JOIN foo_series ON (foo_show.series_id = foo_series.id) WHERE foo_series.year = 2011))))
Ouch! You could express the IN test using a subquery to save a round-trip, but beyond than that there's not much hope of making this look sane. I think this should work (untested): (select-dao 'foo-db-name (:in 'foo.id (:raw "(SELECT DISTINCT foo.id FROM foo_member INNER JOIN foo_show ON (foo_member.id = foo_show.member_id) INNER JOIN foo_series ON (foo_show.series_id = foo_series.id) WHERE foo_series.year = 2011)"))) (Also, your flatten function is probably something like mapcar #'car, which you can more easily do by passing the query macro a second argument of :column)
participants (2)
-
Marijn Haverbeke -
Mitch Berkson