On Aug 31, 10:47 am, Jeremy Evans <[email protected]> wrote: > That's actually something that Sequel doesn't have built in support > for, though it's a fairly simple if you don't mind a gross hack: > > class Post < Sequel::Model > def_dataset_method(:published) > {exclude(:published_at=>nil).where{published_at < Time.now}.qualify} > end > > class User < Sequel::Model > def_dataset_method(:published) > {group(:id).qualify.join(:posts, > :author_id=>:id).filter(Post.published.opts[:where])} > end > > User.published.sql > # SELECT users.* FROM users > # INNER JOIN posts ON (posts.author_id = users.id) > # WHERE ((posts.published_at IS NOT NULL) AND (posts.published_at < > '2010-08-31 09:26:59.383300')) > # GROUP BY users.id > > As to why Sequel doesn't support it by default, I think it's a bad > idea. What if you alias the posts table when joining? What if you > use "& Post.published" without joining to the posts table? > > Honestly, the example they gave could be better, since (IMO) the > proper way to do things is this: > > class Post < Sequel::Model > def_dataset_method(:published) > {exclude(:published_at=>nil).where{published_at < Time.now}} > end > > class User < Sequel::Model > def_dataset_method(:published) > {group(:id).qualify.join(Post.published, :author_id=>:id)} > end > > User.published.sql > # SELECT users.* FROM users > # INNER JOIN ( > # SELECT * FROM posts > # WHERE ((published_at IS NOT NULL) AND > # (published_at < '2010-08-31 09:38:13.746317')) > # ) AS t1 ON (t1.author_id = users.id) > # GROUP BY users.id > > or maybe even better, since you aren't returning anything from related > to posts: > > class Post < Sequel::Model > def_dataset_method(:published) > {exclude(:published_at=>nil).where{published_at < Time.now}} > end > > class User < Sequel::Model > def_dataset_method(:published) > {filter(:id=>Post.published.select(:author_id))} > end > > User.published.sql > # SELECT * FROM users > # WHERE (id IN ( > # SELECT author_id FROM posts > # WHERE ((published_at IS NOT NULL) AND > # (published_at < '2010-08-31 09:46:03.104467')) > # )) > > Jeremy
Thanks Jeremy, I definitely like your alternatives using subqueries. That helps me a lot. Also, I wasn't aware of opts[:where]. I can see why it wouldn't be supported. I think Arel just overwrites or merges stuff where appropriate. I don't think there's anything special going on. -- 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.
