hoodwink.d enhanced
RSS
2.0
XHTML
1.0

RedHanded

Any? and All? for MySQL #

by why in inspect

Seisyll Wyn sends in a tidy MySQL hack:

 SELECT COUNT(*) AS total_students, 
        MAX(student_id = 130220) AS signed_up
 FROM class_signups WHERE class_id = 1347

Can you tell what this does? The MAX aggregate function coupled with the comparison acts like Ruby’s Enumerable#any? Great if you’re retrieving a count and simultaneously want to ask, “Is the current user in that count?”

Similarly, the MIN aggregate function can be leveraged for Enumerable#all?.

 SELECT COUNT(*) AS total_students, 
        MIN(status = 'ACTIVE') AS all_active
 FROM class_signups WHERE class_id = 1347

Not having poked in the MySQL function book for a little while, maybe there are other ways to do this?

said on 30 Apr 2005 at 01:47

not sure why you’d want to word a query such as that suggested by wyn.. it essentially does: a table or index scan on class_signups for only records having class_id of 1347; for each of those records found it will evaluate the expression “student_id = 130220” which is of course going to be boolean, true (1) or false (0), you see? because what you’re asking is: “does this record have a value of 130220 in student_id, yes or no?”; because there is no group by clause and you’re using an aggregate function COUNT each record found to have class_id = 1347 will be returned with a value of 1 for total_students and the value of signed_up with be 0 for every student not having a student_id = 130220 and 1 for every student whose student_id = 130220 … you dig? so i dunno if you’re going to want a result set like: [total_students, signed_up] {1,0} {1,0} {1,0} etc.. etc..

said on 30 Apr 2005 at 09:12

You’re exactly right, chrixian. The MAX helps that value of 1 float to the top. Have you used Ruby’s Enumerable#any? The clause MAX(student_id = 130220) acts just like this code:

 students.any? { |student| student.id == 130220 }

So, while you’re doing a COUNT, you can also see what other criteria the counted set meets. I think the idea here is: MySQL has a pretty limited set of aggregate functions. How can me milk them for more?

said on 30 Apr 2005 at 17:33

well, whatever you put in the MAX function, the count would not change…. seems pretty useless OR I really don’t understand.

I look at the ruby doc for enum.any? and .all? I can see the similarities in the query, but not in the result.

maybe it requires a special version of mysql, I run 4.1 and it does not seems to work…

maybe you can give us more details ????

thanks.

said on 01 May 2005 at 14:56

somekool, it’s not meant to change the COUNT .

It just gives you some additional information alongside the count.

So in one query, you get a COUNT , plus some other information.

said on 03 May 2005 at 01:36

MySQL functions from the manual, for more thinking and digging into this goodness.

why’s call of “How can me milk them for more” is such a challenge. However I want to resist, this just pulls me back in. There is no time for playing around, but still this is like a big ol’ honey-pot of… haX.

Come to think of it, version 4.1 has a host of new stuff I’ve yet to tinker with. Woot.

said on 03 May 2005 at 14:34

Just for the record, the word leveraged is deprecated and will be removed from future versions of English. Use used instead.

said on 05 May 2005 at 02:39

Use SUM instead of MAX and you get a duplicate check for free.

Comments are closed for this entry.