On May 11, 9:03 pm, Elliott <[email protected]> wrote: > Post.filter(:id => 10).union(Post.filter(:topic_id => 10).limit(5)) > returns the following SQL: > > SELECT * FROM `posts` WHERE (`id` = 10) UNION SELECT * FROM `posts` > WHERE (`topic_id` = 10) LIMIT 5 > > This would return five posts total. > > SELECT * FROM `posts` WHERE (`id` = 10) UNION (SELECT * FROM `posts` > WHERE (`topic_id` = 10) LIMIT 5) > > This would return six posts, the first post with id of 10 and five > additional posts. > > I'm unsure how to coerce Sequel into producing the latter, or if I > even can. > > Thanks in advance to anyone who can help.
That SQL syntax would work on PostgreSQL, but it fails on SQLite. On SQLite you'd have to use: SELECT * FROM `posts` WHERE (`id` = 10) UNION SELECT * FROM (SELECT * FROM `posts` WHERE (`topic_id` = 10) LIMIT 5) It's probably a fairly simple change to the PostgreSQL adapter to support what you want. However, the general default case would be significantly more work. Before any changes are made we should probably discuss what the behavior should be for datasets like: Post.filter(:id => 10).limit(10).union(Post.filter(:topic_id => 10).limit(15)).limit(20) My intuition is the Sequel's default behavior should be: SELECT * FROM (SELECT * FROM (SELECT * FROM `posts` WHERE (`id` = 10) LIMIT 10) UNION SELECT * FROM (SELECT * FROM `posts` WHERE (`topic_id` = 10) LIMIT 15)) LIMIT 20 The implementation of that is a little dicey, but probably not too bad. Dataset#limit needs to call from_self if there are any compound statements (UNION, INTERSECT, EXCEPT). Dataset#(union|intersect| except) need to check for an existing limit and change it into a from_self.limit. The above syntax should also work on PostgreSQL. The more condensed form would be: (SELECT * FROM `posts` WHERE (`id` = 10) LIMIT 10) UNION (SELECT * FROM `posts` WHERE (`topic_id` = 10) LIMIT 15) LIMIT 20 Sequel can't really produce that, though, at least not without major changes. The best we could do is: SELECT * FROM ((SELECT * FROM `posts` WHERE (`id` = 10) LIMIT 10) UNION (SELECT * FROM `posts` WHERE (`topic_id` = 10) LIMIT 15)) LIMIT 20 There's probably no point in doing anything special for PostgreSQL, though, as it looks like the plans are identical for each of those 3 queries. 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 -~----------~----~----~----~------~----~------~--~---
