[Dbix-class] Returning search results in a specific order

[Chris Newman]

I have a search like this:
my $ids = [2, 1, 3];
$schema->resultset ('MyTable')->search (id => $ids);

MySQL processes rows in the order they are found in the table so I essentially get back 1, 2, 3.  $ids is really a user-specified list so I'd like to return the results in the same order they originally specified (2, 1, 3).

I'm trying to avoid breaking up the query into lots of tiny queries for two reasons:  1) efficiency, and 2) the rest of my method currently makes use of the resulting ResultSet object.

I discovered one method for controlling the order was to use a built-in function of MySQL:
SELECT * FROM MyTable WHERE id IN (2, 1, 3) ORDER BY FIND_IN_SET(id, '2, 1, 3');

That works great manually!

However, I cannot for the life of me figure out how to do the same thing using DBIx::Class.  My test code looks like this:
$schema->resultset ('MyTable')->search (id => $ids, {
order_by => \'FIND_IN_SET (id, '2, 1, 3')
});

The SQL generated looks right, but DBIC keeps throwing this error about FIND_IN_SET before executing:
DBIx::Class::ResultSet::all(): DBI Exception: DBD::mysql::st execute failed: FUNCTION my_db.FIND_IN_SET does not exist [for Statement "SELECT me.id, me.name, me.active, me.created, me.last_used, me.source, me.priority FROM MyTable me WHERE ( ( me.id = ? OR me.id = ? OR me.id = ? ) ) ORDER BY FIND_IN_SET (me.id, "2, 1, 3")" with ParamValues: 0=2, 1=1, 2=3]

Anyone have any ideas on how to get this to work?

Chris


This email and attachments may contain privileged or confidential information intended only for the addressee(s) indicated. The sender does not waive any of its rights, privileges or protections respecting this information. If you are not the named addressee, an employee, or agent responsible for sending this message to the named addressee (or this message was received by mistake), you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If received in error, please notify us immediately by e-mail, discard any paper copies and delete all electronic files of the email.

Computer viruses can be transmitted via email. The recipient should check this email and any attachments for viruses. Email transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender accepts no liability for any damage caused by any transmitted viruses or errors or omissions in the contents of this message.

Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560 www.overturenetworks.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130430/dc5ad6d9/attachment.htm