-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I have recently needed support for having clauses for some advanced
find queries in my application. I''ve implemented this in a mixin that
implements a new set of finders and query constructors that mirror
the core Active Record, except for the additional support for :having
as an argument. This has reached stability and now works with eager
loading and scopings, so I''d like to extract it from my application
as a patch against Active Record core.
I thought I would talk this through before producing a patch and
missing the mark and having to refine, so a few questions:
1) Can I confirm that :having feels like core functionality? I see
that it is part of the calculation module already, but I need it to
restrict my find results set and not just perform counts and so
forth. Basically I am performing a join and want to use :having to
check that the joined results match a threshold (i.e. HAVING count
(visits.id) > 0).
2) I have identified the following places where :having needs to be
added:
associations.rb - construct_finder_sql_with_included_associations
base.rb - construct_finder_sql
base.rb - validate_find_options
Anywhere else that I am missing?
3) I believe the following tests to be necessary for a good patch,
what am I missing?
- vanilla find with having, single table
- find with having, with join tables. Having against base table
- find with having, with join tables. Having against join table
- find with having, using scope
- find with having, using eager loading
4) Documentation. Update the doc blocks to include the key and
document in the patch ticket?
5) Errors. Do I need to provide any validation on the arguments or
new errors or can I just let AR blow up with exceptions if you ask
for a boneheaded query? The group clauses here become very important.
6) I develop on MySQL exclusively. What kind of cross database
problems do I need to consider? (i.e. I haven''t researched that
having clauses will work everywhere). Is there a database specific
element to what I am trying to accomplish?
Anyway, pointers are appreciated. I have this already working in my
limited world, hopefully it can grow up and be useful to the larger
community...
Cheers,
Blake
- --
Blake Watters
Near-Time, Inc.
http://www.near-time.com/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (Darwin)
iD8DBQFE1Dh7qvuZB2zXNU0RAsUoAKCxSUySagoi1kMMoVLpJs7qEsvMmgCfdEE4
O3Xx14CilzCDaCcNhFVnf4g=I6St
-----END PGP SIGNATURE-----
On Aug 4, 2006, at 11:19 PM, Blake Watters wrote:> I have recently needed support for having clauses for some advanced > find queries in my application. I''ve implemented this in a mixin > that implements a new set of finders and query constructors that > mirror the core Active Record, except for the additional support > for :having as an argument. This has reached stability and now > works with eager loading and scopings, so I''d like to extract it > from my application as a patch against Active Record core.I''m using HAVING just fine already. Just append the HAVING clause to the end of the :group option. find(:all, :readonly => false, :select => "articles.*", :joins => "INNER JOIN taggings t ON articles.id = t.article_id", :conditions => ["t.tag_id IN (?)", tag_list], :order => order, :group => "articles.id HAVING COUNT(articles.id) = # {tag_list.size}") Is there something you''re using HAVING for that needs more flexibility than that? -- Josh Susser http://blog.hasmanythrough.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Interesting. The queries I''m generating get pretty nasty and there''s something to be said for the symmetry offered by a key in the options and documentation, etc. I can probably unwind the :having stuff I''ve done and glue it all onto the :group clause if I''m one of the few people who care about it and use it, but it makes my OCD twitch a little. But I can always leave the :having hackery I''ve added to my query generation stuff tucked away in my lib/ to allay my cleanliness obsession. I think this is a relatively low hanging add, but its pointless for me to toil away extracting in into a patch if nobody cares or wants the extra key. On Aug 5, 2006, at 12:08 PM, Josh Susser wrote:> > On Aug 4, 2006, at 11:19 PM, Blake Watters wrote: >> I have recently needed support for having clauses for some >> advanced find queries in my application. I''ve implemented this in >> a mixin that implements a new set of finders and query >> constructors that mirror the core Active Record, except for the >> additional support for :having as an argument. This has reached >> stability and now works with eager loading and scopings, so I''d >> like to extract it from my application as a patch against Active >> Record core. > > I''m using HAVING just fine already. Just append the HAVING clause > to the end of the :group option. > > find(:all, :readonly => false, > :select => "articles.*", > :joins => "INNER JOIN taggings t ON articles.id = t.article_id", > :conditions => ["t.tag_id IN (?)", tag_list], :order => order, > :group => "articles.id HAVING COUNT(articles.id) = # > {tag_list.size}") > > Is there something you''re using HAVING for that needs more > flexibility than that? > > -- > Josh Susser > http://blog.hasmanythrough.com > > > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.3 (Darwin) iD8DBQFE1NU8qvuZB2zXNU0RAqJNAJwOdTBIeiwoTY3ENT27a8wkz/BJzwCg0D+n OMV6NuOoxgjEB6yGwJUyuY4=ihXG -----END PGP SIGNATURE-----
You could always make it a plugin. Easy to use in your later projects too that way. And if other people like it maybe it''ll be right for inclusion at that time. -- Kevin Clark http://glu.ttono.us
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The other thing that occurs to me is that I can imagine
wanting :having to be subject to scoping. Most of my most interesting
queries these days are coming from nested scopes. Backpacking
onto :group may make this non-trivial, where adding a :having option
and threading it through the Active Record plumbing would let you
work more naturally:
Article.with_scope(:find => { :conditions => "blog_id = 1",
:having
=> ''count(tags.id) > 0'', :include =>
''tags''}) do
Article.with_scope(:find => { :having =>
''count(categories.id) =
#{category_list.size}'', :conditions => [''categories.id IN
(?)'',
category_list], :include => :categories }
Article.find(:all, :group => ''articles.id)) # => SELECT *
from
articles AND categories.id IN (1, 2, 3, 4, 5) LEFT JOIN categories on
categories.id WHERE blog_id = 1 GROUP BY articles.id HAVING count
(categories.id) = 5
end
end
On Aug 5, 2006, at 1:28 PM, Blake Watters wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Interesting. The queries I''m generating get pretty nasty and
> there''s something to be said for the symmetry offered by a key in
> the options and documentation, etc. I can probably unwind
> the :having stuff I''ve done and glue it all onto the :group clause
> if I''m one of the few people who care about it and use it, but it
> makes my OCD twitch a little. But I can always leave the :having
> hackery I''ve added to my query generation stuff tucked away in my
> lib/ to allay my cleanliness obsession.
>
> I think this is a relatively low hanging add, but its pointless for
> me to toil away extracting in into a patch if nobody cares or wants
> the extra key.
>
> On Aug 5, 2006, at 12:08 PM, Josh Susser wrote:
>
>>
>> On Aug 4, 2006, at 11:19 PM, Blake Watters wrote:
>>> I have recently needed support for having clauses for some
>>> advanced find queries in my application. I''ve implemented
this in
>>> a mixin that implements a new set of finders and query
>>> constructors that mirror the core Active Record, except for the
>>> additional support for :having as an argument. This has reached
>>> stability and now works with eager loading and scopings, so
I''d
>>> like to extract it from my application as a patch against Active
>>> Record core.
>>
>> I''m using HAVING just fine already. Just append the HAVING
clause
>> to the end of the :group option.
>>
>> find(:all, :readonly => false,
>> :select => "articles.*",
>> :joins => "INNER JOIN taggings t ON articles.id =
t.article_id",
>> :conditions => ["t.tag_id IN (?)", tag_list], :order
=> order,
>> :group => "articles.id HAVING COUNT(articles.id) = #
>> {tag_list.size}")
>>
>> Is there something you''re using HAVING for that needs more
>> flexibility than that?
>>
>> --
>> Josh Susser
>> http://blog.hasmanythrough.com
>>
>>
>> _______________________________________________
>> Rails-core mailing list
>> Rails-core@lists.rubyonrails.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails-core
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.3 (Darwin)
>
> iD8DBQFE1NU8qvuZB2zXNU0RAqJNAJwOdTBIeiwoTY3ENT27a8wkz/BJzwCg0D+n
> OMV6NuOoxgjEB6yGwJUyuY4> =ihXG
> -----END PGP SIGNATURE-----
> _______________________________________________
> Rails-core mailing list
> Rails-core@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails-core
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (Darwin)
iD8DBQFE1NnbqvuZB2zXNU0RAgGgAJoDfjC+KOMp18OTdvQP9BsCBcIkBACgz2Tc
+FWx02MUovgenC12/ZO6QHo=dfcH
-----END PGP SIGNATURE-----
>From the OP''s message...."This has reached stability and now works with eager loading and scopings," On 8/5/06, Blake Watters <blake@near-time.com> wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > The other thing that occurs to me is that I can imagine > wanting :having to be subject to scoping. Most of my most interesting > queries these days are coming from nested scopes. Backpacking > onto :group may make this non-trivial, where adding a :having option > and threading it through the Active Record plumbing would let you > work more naturally: > > Article.with_scope(:find => { :conditions => "blog_id = 1", :having > => ''count(tags.id) > 0'', :include => ''tags''}) do > Article.with_scope(:find => { :having => ''count(categories.id) > #{category_list.size}'', :conditions => [''categories.id IN (?)'', > category_list], :include => :categories } > Article.find(:all, :group => ''articles.id)) # => SELECT * from > articles AND categories.id IN (1, 2, 3, 4, 5) LEFT JOIN categories on > categories.id WHERE blog_id = 1 GROUP BY articles.id HAVING count > (categories.id) = 5 > end > end > > On Aug 5, 2006, at 1:28 PM, Blake Watters wrote: > > > -----BEGIN PGP SIGNED MESSAGE----- > > Hash: SHA1 > > > > Interesting. The queries I''m generating get pretty nasty and > > there''s something to be said for the symmetry offered by a key in > > the options and documentation, etc. I can probably unwind > > the :having stuff I''ve done and glue it all onto the :group clause > > if I''m one of the few people who care about it and use it, but it > > makes my OCD twitch a little. But I can always leave the :having > > hackery I''ve added to my query generation stuff tucked away in my > > lib/ to allay my cleanliness obsession. > > > > I think this is a relatively low hanging add, but its pointless for > > me to toil away extracting in into a patch if nobody cares or wants > > the extra key. > > > > On Aug 5, 2006, at 12:08 PM, Josh Susser wrote: > > > >> > >> On Aug 4, 2006, at 11:19 PM, Blake Watters wrote: > >>> I have recently needed support for having clauses for some > >>> advanced find queries in my application. I''ve implemented this in > >>> a mixin that implements a new set of finders and query > >>> constructors that mirror the core Active Record, except for the > >>> additional support for :having as an argument. This has reached > >>> stability and now works with eager loading and scopings, so I''d > >>> like to extract it from my application as a patch against Active > >>> Record core. > >> > >> I''m using HAVING just fine already. Just append the HAVING clause > >> to the end of the :group option. > >> > >> find(:all, :readonly => false, > >> :select => "articles.*", > >> :joins => "INNER JOIN taggings t ON articles.id = t.article_id", > >> :conditions => ["t.tag_id IN (?)", tag_list], :order => order, > >> :group => "articles.id HAVING COUNT(articles.id) = # > >> {tag_list.size}") > >> > >> Is there something you''re using HAVING for that needs more > >> flexibility than that? > >> > >> -- > >> Josh Susser > >> http://blog.hasmanythrough.com > >> > >> > >> _______________________________________________ > >> Rails-core mailing list > >> Rails-core@lists.rubyonrails.org > >> http://lists.rubyonrails.org/mailman/listinfo/rails-core > > > > -----BEGIN PGP SIGNATURE----- > > Version: GnuPG v1.4.3 (Darwin) > > > > iD8DBQFE1NU8qvuZB2zXNU0RAqJNAJwOdTBIeiwoTY3ENT27a8wkz/BJzwCg0D+n > > OMV6NuOoxgjEB6yGwJUyuY4> > =ihXG > > -----END PGP SIGNATURE----- > > _______________________________________________ > > Rails-core mailing list > > Rails-core@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails-core > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.3 (Darwin) > > iD8DBQFE1NnbqvuZB2zXNU0RAgGgAJoDfjC+KOMp18OTdvQP9BsCBcIkBACgz2Tc > +FWx02MUovgenC12/ZO6QHo> =dfcH > -----END PGP SIGNATURE----- > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Right, because I''ve hacked the support in there for my app. The whole question is whether I should leave this mess in my lib, make it a plugin or patch it against core. John Susser pointed out that you can do it by gluing onto the :group key and my counterpoint is that scopings make the case for :having over :group => ''foo having count (id)''. I was just trying to illustrate why :having is a win over group by piggybacking. I don''t really see the point of sending my own sentences back to me when you aren''t illustrating a viable point or contributing to the discussion. On Aug 5, 2006, at 1:54 PM, Andrew Kaspick wrote:>> From the OP''s message.... > > "This has reached stability and now works with eager loading and > scopings," > > On 8/5/06, Blake Watters <blake@near-time.com> wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> The other thing that occurs to me is that I can imagine >> wanting :having to be subject to scoping. Most of my most interesting >> queries these days are coming from nested scopes. Backpacking >> onto :group may make this non-trivial, where adding a :having option >> and threading it through the Active Record plumbing would let you >> work more naturally: >> >> Article.with_scope(:find => { :conditions => "blog_id = 1", :having >> => ''count(tags.id) > 0'', :include => ''tags''}) do >> Article.with_scope(:find => { :having => ''count(categories.id) >> #{category_list.size}'', :conditions => [''categories.id IN (?)'', >> category_list], :include => :categories } >> Article.find(:all, :group => ''articles.id)) # => SELECT * from >> articles AND categories.id IN (1, 2, 3, 4, 5) LEFT JOIN categories on >> categories.id WHERE blog_id = 1 GROUP BY articles.id HAVING count >> (categories.id) = 5 >> end >> end >> >> On Aug 5, 2006, at 1:28 PM, Blake Watters wrote: >> >> > -----BEGIN PGP SIGNED MESSAGE----- >> > Hash: SHA1 >> > >> > Interesting. The queries I''m generating get pretty nasty and >> > there''s something to be said for the symmetry offered by a key in >> > the options and documentation, etc. I can probably unwind >> > the :having stuff I''ve done and glue it all onto the :group clause >> > if I''m one of the few people who care about it and use it, but it >> > makes my OCD twitch a little. But I can always leave the :having >> > hackery I''ve added to my query generation stuff tucked away in my >> > lib/ to allay my cleanliness obsession. >> > >> > I think this is a relatively low hanging add, but its pointless for >> > me to toil away extracting in into a patch if nobody cares or wants >> > the extra key. >> > >> > On Aug 5, 2006, at 12:08 PM, Josh Susser wrote: >> > >> >> >> >> On Aug 4, 2006, at 11:19 PM, Blake Watters wrote: >> >>> I have recently needed support for having clauses for some >> >>> advanced find queries in my application. I''ve implemented this in >> >>> a mixin that implements a new set of finders and query >> >>> constructors that mirror the core Active Record, except for the >> >>> additional support for :having as an argument. This has reached >> >>> stability and now works with eager loading and scopings, so I''d >> >>> like to extract it from my application as a patch against Active >> >>> Record core. >> >> >> >> I''m using HAVING just fine already. Just append the HAVING clause >> >> to the end of the :group option. >> >> >> >> find(:all, :readonly => false, >> >> :select => "articles.*", >> >> :joins => "INNER JOIN taggings t ON articles.id = >> t.article_id", >> >> :conditions => ["t.tag_id IN (?)", tag_list], :order => order, >> >> :group => "articles.id HAVING COUNT(articles.id) = # >> >> {tag_list.size}") >> >> >> >> Is there something you''re using HAVING for that needs more >> >> flexibility than that? >> >> >> >> -- >> >> Josh Susser >> >> http://blog.hasmanythrough.com >> >> >> >> >> >> _______________________________________________ >> >> Rails-core mailing list >> >> Rails-core@lists.rubyonrails.org >> >> http://lists.rubyonrails.org/mailman/listinfo/rails-core >> > >> > -----BEGIN PGP SIGNATURE----- >> > Version: GnuPG v1.4.3 (Darwin) >> > >> > iD8DBQFE1NU8qvuZB2zXNU0RAqJNAJwOdTBIeiwoTY3ENT27a8wkz/BJzwCg0D+n >> > OMV6NuOoxgjEB6yGwJUyuY4>> > =ihXG >> > -----END PGP SIGNATURE----- >> > _______________________________________________ >> > Rails-core mailing list >> > Rails-core@lists.rubyonrails.org >> > http://lists.rubyonrails.org/mailman/listinfo/rails-core >> >> -----BEGIN PGP SIGNATURE----- >> Version: GnuPG v1.4.3 (Darwin) >> >> iD8DBQFE1NnbqvuZB2zXNU0RAgGgAJoDfjC+KOMp18OTdvQP9BsCBcIkBACgz2Tc >> +FWx02MUovgenC12/ZO6QHo>> =dfcH >> -----END PGP SIGNATURE----- >> _______________________________________________ >> Rails-core mailing list >> Rails-core@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails-core >> > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.3 (Darwin) iD8DBQFE1N3KqvuZB2zXNU0RAklVAJ901QV+bNBPBnq2GrlXdTfDjBA2bACfS4Nx p8Mz6OagBAUeRKrPZyysDbk=wpsa -----END PGP SIGNATURE-----
Didn''t realize you were the one who was the OP. Regardless, your comment is confusing. You mention you have it working with scoping in your first message and then you ask if it should work with scoping??? Ignore my message then. As you were. :) On 8/5/06, Blake Watters <blake@near-time.com> wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Right, because I''ve hacked the support in there for my app. The whole > question is whether I should leave this mess in my lib, make it a > plugin or patch it against core. John Susser pointed out that you can > do it by gluing onto the :group key and my counterpoint is that > scopings make the case for :having over :group => ''foo having count > (id)''. I was just trying to illustrate why :having is a win over > group by piggybacking. > > I don''t really see the point of sending my own sentences back to me > when you aren''t illustrating a viable point or contributing to the > discussion. > > On Aug 5, 2006, at 1:54 PM, Andrew Kaspick wrote: > > >> From the OP''s message.... > > > > "This has reached stability and now works with eager loading and > > scopings," > > > > On 8/5/06, Blake Watters <blake@near-time.com> wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- > >> Hash: SHA1 > >> > >> The other thing that occurs to me is that I can imagine > >> wanting :having to be subject to scoping. Most of my most interesting > >> queries these days are coming from nested scopes. Backpacking > >> onto :group may make this non-trivial, where adding a :having option > >> and threading it through the Active Record plumbing would let you > >> work more naturally: > >> > >> Article.with_scope(:find => { :conditions => "blog_id = 1", :having > >> => ''count(tags.id) > 0'', :include => ''tags''}) do > >> Article.with_scope(:find => { :having => ''count(categories.id) > >> #{category_list.size}'', :conditions => [''categories.id IN (?)'', > >> category_list], :include => :categories } > >> Article.find(:all, :group => ''articles.id)) # => SELECT * from > >> articles AND categories.id IN (1, 2, 3, 4, 5) LEFT JOIN categories on > >> categories.id WHERE blog_id = 1 GROUP BY articles.id HAVING count > >> (categories.id) = 5 > >> end > >> end > >> > >> On Aug 5, 2006, at 1:28 PM, Blake Watters wrote: > >> > >> > -----BEGIN PGP SIGNED MESSAGE----- > >> > Hash: SHA1 > >> > > >> > Interesting. The queries I''m generating get pretty nasty and > >> > there''s something to be said for the symmetry offered by a key in > >> > the options and documentation, etc. I can probably unwind > >> > the :having stuff I''ve done and glue it all onto the :group clause > >> > if I''m one of the few people who care about it and use it, but it > >> > makes my OCD twitch a little. But I can always leave the :having > >> > hackery I''ve added to my query generation stuff tucked away in my > >> > lib/ to allay my cleanliness obsession. > >> > > >> > I think this is a relatively low hanging add, but its pointless for > >> > me to toil away extracting in into a patch if nobody cares or wants > >> > the extra key. > >> > > >> > On Aug 5, 2006, at 12:08 PM, Josh Susser wrote: > >> > > >> >> > >> >> On Aug 4, 2006, at 11:19 PM, Blake Watters wrote: > >> >>> I have recently needed support for having clauses for some > >> >>> advanced find queries in my application. I''ve implemented this in > >> >>> a mixin that implements a new set of finders and query > >> >>> constructors that mirror the core Active Record, except for the > >> >>> additional support for :having as an argument. This has reached > >> >>> stability and now works with eager loading and scopings, so I''d > >> >>> like to extract it from my application as a patch against Active > >> >>> Record core. > >> >> > >> >> I''m using HAVING just fine already. Just append the HAVING clause > >> >> to the end of the :group option. > >> >> > >> >> find(:all, :readonly => false, > >> >> :select => "articles.*", > >> >> :joins => "INNER JOIN taggings t ON articles.id > >> t.article_id", > >> >> :conditions => ["t.tag_id IN (?)", tag_list], :order => order, > >> >> :group => "articles.id HAVING COUNT(articles.id) = # > >> >> {tag_list.size}") > >> >> > >> >> Is there something you''re using HAVING for that needs more > >> >> flexibility than that? > >> >> > >> >> -- > >> >> Josh Susser > >> >> http://blog.hasmanythrough.com > >> >> > >> >> > >> >> _______________________________________________ > >> >> Rails-core mailing list > >> >> Rails-core@lists.rubyonrails.org > >> >> http://lists.rubyonrails.org/mailman/listinfo/rails-core > >> > > >> > -----BEGIN PGP SIGNATURE----- > >> > Version: GnuPG v1.4.3 (Darwin) > >> > > >> > iD8DBQFE1NU8qvuZB2zXNU0RAqJNAJwOdTBIeiwoTY3ENT27a8wkz/BJzwCg0D+n > >> > OMV6NuOoxgjEB6yGwJUyuY4> >> > =ihXG > >> > -----END PGP SIGNATURE----- > >> > _______________________________________________ > >> > Rails-core mailing list > >> > Rails-core@lists.rubyonrails.org > >> > http://lists.rubyonrails.org/mailman/listinfo/rails-core > >> > >> -----BEGIN PGP SIGNATURE----- > >> Version: GnuPG v1.4.3 (Darwin) > >> > >> iD8DBQFE1NnbqvuZB2zXNU0RAgGgAJoDfjC+KOMp18OTdvQP9BsCBcIkBACgz2Tc > >> +FWx02MUovgenC12/ZO6QHo> >> =dfcH > >> -----END PGP SIGNATURE----- > >> _______________________________________________ > >> Rails-core mailing list > >> Rails-core@lists.rubyonrails.org > >> http://lists.rubyonrails.org/mailman/listinfo/rails-core > >> > > _______________________________________________ > > Rails-core mailing list > > Rails-core@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails-core > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.3 (Darwin) > > iD8DBQFE1N3KqvuZB2zXNU0RAklVAJ901QV+bNBPBnq2GrlXdTfDjBA2bACfS4Nx > p8Mz6OagBAUeRKrPZyysDbk> =wpsa > -----END PGP SIGNATURE----- > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Aug 4, 2006, at 11:19 PM, Blake Watters wrote:> I have recently needed support for having clauses for some advanced > find queries in my application. I''ve implemented this in a mixin > that implements a new set of finders and query constructors that > mirror the core Active Record, except for the additional support > for :having as an argument. This has reached stability and now > works with eager loading and scopings, so I''d like to extract it > from my application as a patch against Active Record core. > I thought I would talk this through before producing a patch and > missing the mark and having to refine, so a few questions: > 1) Can I confirm that :having feels like core functionality? I see > that it is part of the calculation module already, but I need it to > restrict my find results set and not just perform counts and so > forth. Basically I am performing a join and want to use :having to > check that the joined results match a threshold (i.e. HAVING count > (visits.id) > 0).Sounds good, Blake. Do have a patch, or is it tied into your app?> 2) I have identified the following places where :having needs to be > added: > associations.rb - construct_finder_sql_with_included_associations > base.rb - construct_finder_sql > base.rb - validate_find_optionsconstruct_finder_sql + :having is enough to support Calculations too, so we can DRY out its duplicate sql construction.> Anywhere else that I am missing? > 3) I believe the following tests to be necessary for a good patch, > what am I missing? > - vanilla find with having, single table > - find with having, with join tables. Having against base table > - find with having, with join tables. Having against join table > - find with having, using scope > - find with having, using eager loading+ behavior in nested scopes> 4) Documentation. Update the doc blocks to include the key and > document in the patch ticket?Yes, please.> 5) Errors. Do I need to provide any validation on the arguments or > new errors or can I just let AR blow up with exceptions if you ask > for a boneheaded query? The group clauses here become very important.You can just let the db raise exceptions as usual.> 6) I develop on MySQL exclusively. What kind of cross database > problems do I need to consider? (i.e. I haven''t researched that > having clauses will work everywhere). Is there a database specific > element to what I am trying to accomplish?Please give PostgreSQL a shot. If it works there, it''s likely to work elsewhere. Thanks! jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.3 (Darwin) iD8DBQFE1P8AAQHALep9HFYRArNaAKC+/XtvYvBJVaTTCxxTxnPh208CxgCgxGmA 6L2i1CzUeh+jP1s7z9GTA+Y=wp+t -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Aug 5, 2006, at 4:26 PM, Jeremy Kemper wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Aug 4, 2006, at 11:19 PM, Blake Watters wrote: >> I have recently needed support for having clauses for some >> advanced find queries in my application. I''ve implemented this in >> a mixin that implements a new set of finders and query >> constructors that mirror the core Active Record, except for the >> additional support for :having as an argument. This has reached >> stability and now works with eager loading and scopings, so I''d >> like to extract it from my application as a patch against Active >> Record core. >> I thought I would talk this through before producing a patch and >> missing the mark and having to refine, so a few questions: >> 1) Can I confirm that :having feels like core functionality? I see >> that it is part of the calculation module already, but I need it >> to restrict my find results set and not just perform counts and so >> forth. Basically I am performing a join and want to use :having to >> check that the joined results match a threshold (i.e. HAVING count >> (visits.id) > 0). > > Sounds good, Blake. Do have a patch, or is it tied into your app? >Currently it''s baked into a mixin that decorates the behavior onto models I need to generate the queries with. Should just be a matter of factoring out into a patch and recreating the appropriate tests.>> 2) I have identified the following places where :having needs to >> be added: >> associations.rb - construct_finder_sql_with_included_associations >> base.rb - construct_finder_sql >> base.rb - validate_find_options > > construct_finder_sql + :having is enough to support Calculations > too, so we can DRY out its duplicate sql construction. >Great, I''ll look at this as I make the patch.>> Anywhere else that I am missing? >> 3) I believe the following tests to be necessary for a good patch, >> what am I missing? >> - vanilla find with having, single table >> - find with having, with join tables. Having against base table >> - find with having, with join tables. Having against join table >> - find with having, using scope >> - find with having, using eager loading > > + behavior in nested scopes > >> 4) Documentation. Update the doc blocks to include the key and >> document in the patch ticket? > > Yes, please. > >> 5) Errors. Do I need to provide any validation on the arguments or >> new errors or can I just let AR blow up with exceptions if you ask >> for a boneheaded query? The group clauses here become very important. > > You can just let the db raise exceptions as usual. > >> 6) I develop on MySQL exclusively. What kind of cross database >> problems do I need to consider? (i.e. I haven''t researched that >> having clauses will work everywhere). Is there a database specific >> element to what I am trying to accomplish? > > Please give PostgreSQL a shot. If it works there, it''s likely to > work elsewhere. >No problem, I''ll merge Postgres on one of my boxes and give her a whirl.> Thanks! > jeremy > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.3 (Darwin) > > iD8DBQFE1P8AAQHALep9HFYRArNaAKC+/XtvYvBJVaTTCxxTxnPh208CxgCgxGmA > 6L2i1CzUeh+jP1s7z9GTA+Y> =wp+t > -----END PGP SIGNATURE----- > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.3 (Darwin) iD8DBQFE1SV1qvuZB2zXNU0RAh23AJ4hCw8z0NfFfeoB6kPpqazgNcYeAACeIQO1 VL9ht4ruy4hHaeiMLsCulO8=Clny -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Kevin Clark wrote:> You could always make it a plugin. Easy to use in your later projects > too that way. And if other people like it maybe it''ll be right for > inclusion at that time. >I agree with Kevin. Make it a plugin, or submit a patch to me and I''ll make it apart of my plugin, ActiveRecord Extensions and I''ll give you credit for your addition. http://blogs.mktec.com/zdennis/articles/2006/08/03/activerecord-optimizations-er-extensions-0-0-3 I am working on documentation and some additional features for 0.4.0. So I will have a better page of documentation and my efforts up soon. I am not posting this as a gem yet on rubyforge until it becomes a little more mature in terms of Db support (right now it supports MySQL only) and I want to providing official PostgreSQL support as well (SQL Server, Oracle, etc. can wait). If you''re interested in this email me privately. - --- enuf of my spiele --- Also, I think :having makes logical sense to move it out of the :group value. It separates different pieces of the query string into logical blocks that people can think about and visually separate. If someone is going to say, but i can already do that by appending "HAVING ..." to my :group clause, and that is the reason they oppose your suggestion, then maybe they should not use :select, :conditions or :join either, and they should simply append things to their find_by_sql statement. I do think that :having should get rid of a person having to type "HAVING", but my guess if that you already have it do that. ;) Zach -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE3f3zMyx0fW1d8G0RAmUuAJsFjfG3GnovYKhg4Y8P/PBiwpXrVACfYJpQ xzT0IfAu1ngq6FCh90Ikb1s=bHi1 -----END PGP SIGNATURE-----