On Mar 19, 4:18 pm, "Aaron D. Gifford" <[email protected]> wrote: > Is there a non-raw-SQL way to do this with Sequel? > > SELECT * FROM (SELECT * FROM table WHERE ugly = 2 ORDER BY priority > ASC) AS subquery GROUP BY name HAVING status = 1
This should give the same results: db[:table].filter(:ugly => 2).order(:priority).from_self.group(:name).having(:status=>1) > I can get the subquery built okay: > > db[:table].filter(:ugly => 2).order(:priority.asc) > > And if I wrap it: > > db.select(db[:table].filter(:ugly => > 2).order(:priority.asc)).group(:name).having(:status=>1) > > I'm nearly there. But MySQL won't execute that without the subquery > named. It will raise: > > Sequel::DatabaseError: Mysql::Error: You have an error in your SQL > syntax; check the manual that corresponds to your MySQL server version > for the right syntax to use near 'GROUP BY `name`... Because you put the subselect in the select clause instead of in the FROM clause. Your code yields the following SQL: SELECT (SELECT * FROM table WHERE (ugly = 2) ORDER BY priority ASC) GROUP BY name HAVING (status = 1) The from_self example above is the way I'd do it, but the longer way is: db.from(db[:table].filter(:ugly => 2).order(:priority).as(:subquery)).group(:name).having(:status=>1) Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
