Search code examples
sqlperldbix-class

DBIx::Class : Resultset order_by based upon existence of a value in the list

I am using DBIx::Class and I have got a ResultSet. I like to re-order the ResultSet. I like to check a particular column "City" against a fix list of values ("London", "New York" "Tokyo") If city is found in the list of values I like to move that result to the top group. If city is not found, I like to move that result to the bottom group in the ResultSet.

Solution

  • ORDER BY expr might be what you're looking for.

    For example, here a table:

    mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | London    |
|  2 | Paris     |
|  3 | Tokio     |
|  4 | Rome      |
|  5 | Amsterdam |
+----+-----------+

    Here the special ordering:

    mysql> select * from test order by name = 'London' desc, 
                                   name = 'Paris'  desc, 
                                   name = 'Amsterdam' desc;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | London    |
|  2 | Paris     |
|  5 | Amsterdam |
|  3 | Tokio     |
|  4 | Rome      |
+----+-----------+

    Translating this into a ResultSet method:

    $schema->resultset('Test')->search(
    {},
    {order_by => {-desc => q[name in ('London', 'New York', 'Tokyo')] }}
);