Forum: Rails-core (closed, excessive spam) Adding :having to Active Record

5a3762de21f1638e6ab4f61327ba9f33?d=identicon&s=25 Blake Watters (Guest)
on 2006-08-05 08:20
(Received via mailing list)
-----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-----
9f0f89bbd9e1ecfbaab6584e429b7a2f?d=identicon&s=25 Josh Susser (jsusser)
on 2006-08-05 18:09
(Received via mailing list)
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
5a3762de21f1638e6ab4f61327ba9f33?d=identicon&s=25 Blake Watters (Guest)
on 2006-08-05 19:30
(Received via mailing list)
-----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:

>
> Is there something you're using HAVING for that needs more
> 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-----
6f792b946bbf30845314eb501da5e040?d=identicon&s=25 Kevin Clark (Guest)
on 2006-08-05 19:39
(Received via mailing list)
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.
5a3762de21f1638e6ab4f61327ba9f33?d=identicon&s=25 Blake Watters (Guest)
on 2006-08-05 19:48
(Received via mailing list)
-----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:

> lib/ to allay my cleanliness obsession.
>>> advanced find queries in my application. I've implemented this in
>>   find(:all, :readonly => false,
>> Josh Susser
>
> 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-----
E51640ebc75a902eefb2d7bc29976ca6?d=identicon&s=25 Andrew Kaspick (akaspick)
on 2006-08-05 19:54
(Received via mailing list)
>From the OP's message....

"This has reached stability and now works with eager loading and
scopings,"
5a3762de21f1638e6ab4f61327ba9f33?d=identicon&s=25 Blake Watters (Guest)
on 2006-08-05 20:06
(Received via mailing list)
-----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:

>> wanting :having to be subject to scoping. Most of my most interesting
>>        Article.find(:all, :group => 'articles.id)) # => SELECT * from
>> >
>> > me to toil away extracting in into a patch if nobody cares or wants
>> >>> additional support for :having as an argument. This has reached
>> t.article_id",
>> >>
>> > OMV6NuOoxgjEB6yGwJUyuY4=
>> iD8DBQFE1NnbqvuZB2zXNU0RAgGgAJoDfjC+KOMp18OTdvQP9BsCBcIkBACgz2Tc
> 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-----
E51640ebc75a902eefb2d7bc29976ca6?d=identicon&s=25 Andrew Kaspick (akaspick)
on 2006-08-05 20:15
(Received via mailing list)
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. :)
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 Jeremy Kemper (Guest)
on 2006-08-05 22:27
(Received via mailing list)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Aug 4, 2006, at 11:19 PM, Blake Watters wrote:
> 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_options

construct_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-----
5a3762de21f1638e6ab4f61327ba9f33?d=identicon&s=25 Blake Watters (Guest)
on 2006-08-06 01:12
(Received via mailing list)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


On Aug 5, 2006, at 4:26 PM, Jeremy Kemper wrote:

>> like to extract it from my application as a patch against Active
> 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.

>
>
>> 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.

> 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-----
F0223b1193ecc3a935ce41a1edd72e42?d=identicon&s=25 zdennis (Guest)
on 2006-08-15 14:18
(Received via mailing list)
-----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...

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-----
This topic is locked and can not be replied to.