When I write complex queries using Sequel, I occasionally find myself
wanting to wrap virtual parentheses around the dataset so far before
performing additional joins and selects on it.
For example:
lol_posts = DB[:posts].grep(:name,'%can has%').select(:author_id)
lol_authors = lol_posts.inner_join(:authors,:id=>:author_id)
p lol_authors.select(:name).all.length
#=> SELECT name FROM posts
#=> INNER JOIN authors ON (authors.id = posts.author_id)
#=> WHERE ((name LIKE '%omg%'))"
#=> SQLite3::SQLException ambiguous column name: name
(Sequel::DatabaseError)
The problem with the above is that :posts and :authors both have a
'name' column, and my intent to select just the author_id from posts
was overridden by the later select(:name).
The best way I've come up with to work around this is to alias the
dataset and then create a new dataset from the alias:
lol_posts = DB.dataset.from( DB[:posts].grep(:name,'%can has
%').select(:author_id) )
lol_authors = lol_posts.inner_join(:authors,:id=>:author_id)
p lol_authors.select(:name).all.length
#=> SELECT name FROM
#=> (SELECT author_id FROM posts WHERE ((name LIKE '%can has%')))
AS 'lol_posts'
#=> INNER JOIN authors ON (authors.id = lol_posts.author_id)
However, I find it cumbersome to repeatedly write DB.dataset.from
( ....as(foo) ). It's verbose and it also has to wrap the dataset
instead of being chainable. So, I propose adding a new Dataset#as_ds
method that creates the aliased dataset for you:
lol_authors = lol_posts.as_ds(:lol_posts).inner_join(:authors, :id
=> :author_id).select(:name)
The implementation is simple:
def as_ds(aliaz)
db.dataset.from( as( aliaz ) )
end
Here's a diff against the current 3.2.0 git source to implement it
with documentation:
http://pastie.org/537627
Thoughts?
Alternatively:
* Is there an existing, better way to do this already?
* Would it be possible to change Dataset#as to return a Dataset while
also allowing the result to be used where AliasedExpression is
currently used, without adding an additional level of indirection in
the result? (This is how I personally had hoped and expected #as to
work.)
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
