Any? and All? for MySQL #
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?
chrixian
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..
why
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?
somekool
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.
timsuth
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.
olleolleolle
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.
pmcm
Just for the record, the word leveraged is deprecated and will be removed from future versions of English. Use used instead.
dca
Use SUM instead of MAX and you get a duplicate check for free.
Comments are closed for this entry.