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

Len Jaffe lenjaffe at jaffesystems.com
Tue Apr 30 18:58:04 GMT 2013


order them by the the length of the name of the number descending, and then
alphabetically by the cardinal name of the number ascending:

1,2,3
one, two, three
two, one three
2,1,3

len.


On Tue, Apr 30, 2013 at 2:21 PM, Chris Newman <
Chris.Newman at overturenetworks.com> wrote:

>  I have a search like this:
>
> my $ids =3D [2, 1, 3];
>
> $schema->resultset (=91MyTable=92)->search (id =3D> $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=92d like to return the results in the same order they originally specif=
ied (2,
> 1, 3).
>
>
>
> I=92m 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 (=91MyTable=92)->search (id =3D> $ids, {
>
> order_by =3D> \=92FIND_IN_SET (id, =912, 1, 3=92)
>
> });
>
>
>
> 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 =3D ? OR me.id =3D ? OR me.id=
 =3D ?
> ) ) ORDER BY FIND_IN_SET (me.id, "2, 1, 3")" with ParamValues: 0=3D2, 1=
=3D1,
> 2=3D3]
>
>
>
> 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 a=
nd
> 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 intercepte=
d,
> 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
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



-- =

lenjaffe at jaffesystems.com   614-404-4214   www.lenjaffe.com
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Perl Advent Planet <http://www.lenjaffe.com/PerlAdventPlanet/> - Advent
Calendars: Perlish and otherwise.
Greenbar <http://www.greenbartraining.org/>: Grubmaster: 2012-2009, Grub
Asst: 2008, Trained: 2007.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130430/d8a=
b7c9b/attachment.htm


More information about the DBIx-Class mailing list