As explained in part 1 of this post, I was storing some user block settings in a database table with the following integer fields:

id, user_id, to_user_id, block_type

I wanted a really quick way to load these up into ruby, and really didn’t want to create activerecord objects for each block as a user could have lots and lots of them, and ultimately, they were only going to make their way to a flash client or red5 media server.

I decided it’d be best to get them out as csv values and therefore get them out of mysql using the conneciton object of an activerecord class, something like:

  1. Block.connection.find_all "select foo from bar;"

But what was the query going to be?

When I did this on sql server I had to have a stored proc to process this as the implementation was far more heavy (I had a lot more stored procs in my .net days – I have none now).

After asking about and checking around I came to this solution which has worked very well:

  1. Block.connection.find_all "select group_concat(to_user_id,’,’, block_type) from blocks where user_id=#{user_id} group by user_id "

the group_concat method will automatically take all of the rows from the query result, and concatanate the fields I want into one long string. This is perfect for my needs as it means that rails can very quickly get this one value back and pass it straight out to where it needs to go with no processing on my rails whatsoever.

Don’t get me wrong, I love the ORM, but why waste processing power? I know there are lots of other groovy little mysql functions like this out there, feel free to comment on any you think I might be interested in!!