This will no doubt be old hat to mysql and postgres users, but I came from sql server..

I was working on a site called visualchat.co.uk which allows users to chat to one another – and, as you might expect we get our fair share of nob ends.. As such we give our users the ability to block one another.

I recently made another version of this site and moved the entire stack from asp.net, sql server flashcom, to mysql, ruby on rails and red5, I was pleasantly surprised when it came to implementing the blocks in mysql.

I store the blocks in a table with the following field:

id, user_id, to_user_id, block_type

All of these are integers.

I found with mysql I can create an index on user_id and to_user_id

1. CREATE UNIQUE INDEX user_block ON blocks(user_id, to_user_id)

Now users can change their blocks to block, or unblock individuals, and also use that integer setting to indicate if they want to turn a clean chat filter on or off, as these might change several times in a users history, I can’t (in sql server anyway) do a straight update, I need to do a select, and then update or insert depending if the value is present.

I optimised that in sql server using temp tables, but I always was unhappy with the way it was implemented. When I moved to mysql I found that it has this great feature called ON DUPLICATE KEY UPDATE.

It allows you to insert into a table, and when an insert would cause a duplicate key, update the existing row. Now this is just what I wanted – I could now do code like:

INSERT INTO blocks (user_id, to_user_id, block_type) VALUES (1,2,6) ON DUPLICATE KEY UPDATE block_type=7;

This has made my life so much easier, I can now get my chat server to dump all of the changed block settings into a given session into a temp table and use this niffty command to persist the blocks!

I’m sure I’ll be using this again in the future too – super handy!

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