korma queries to sql plain queries..as-sql must works??

[jete]

Hi everybody, sorry if it's a bit noob question, but I'm interested in get the sql query from my clojure/korma query...

I'm using as-sql but I can't get it works...basically my query is

(select groups

        (with subtopics (with articles))

        (where {:gid "test-group"}))

not sure how use as-sql with the with statement...if I try it

(-> (select* groups)(with subtopics (with articles)) (where {:gid "test-groups"}) (as-sql))

I get => "SELECT \"groups\".* FROM \"groups\" WHERE (\"groups\".\"gid\" = ?)"

not with/join...

can I get the sql with korma or I've a wrong concept about the as-sql function??

thanks!!

[Immo Heikkinen]

Relationships will be fetch lazily with separate selects. Try `dry-run`, it will show give you an idea about select statements that will be executed.

--
You received this message because you are subscribed to the Google Groups "Korma" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlkorma+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jete]

yes..thanks for the reply Immo...basically I need return the full sql query in a json response... I don't know if it's possible, I found a similar question here http://stackoverflow.com/questions/15507062/how-do-you-make-korma-output-the-sql-it-would-execute/15508998#15508998

I try using dry-run and sql-only, but I always get the query for retrieve my group< I suppose than I'm building the first query for get the group but not the successive queries for subtopics and articles...am I doing something wrong??

probably I will use a view in postgreSQL and use a join, I think than would have better performance, but I'm  curious if I can build my sql queries with clojure cause I'm using a small server which needs return the formatted query in a json response..

thanks!!!...  

[Immo Heikkinen]

Let me explain what happens in Korma when you're executing the given select query. There was no information about the type of relations between groups and subtopics, and subtopics and articles, but I think it is safe to assume that they are has-many relations.

1. Korma selects groups with given gid (select * from groups where gid = ?)

2. For each row (group) in the result set, Korma selects subtopics related to it (select * from subtopic where group_id = ?)

3. For each row in subtopic result set, Korma selecte articles related to it (select * from articles where subtopic_id = ?)

So as you can see, there's no single sql statement that could be returned `as-sql` function, but three different sql statements and they will be executed zero to n times depending on the result count and how lazy sequences are realised.

You could also use `join` instead of `with` in your query (results in single sql statement) but it looks like it cannot be nested in this way. 

[jete]

Ups, ok, I got it...finally ;D...I will use views with postgreSQL, it's ok...thanks