Order mySQL query (yii db query) based on array values

Question

I'm currently working on a Craft Commerce site with a custom plugin (and custom table) and trying to order the db query results based on a 'code' value from an array (the array data is not available in the database since this is coming from an external api).

Items containing code '1' should be returned before '10' and also alphabetically by name.

I have an array containing the ids that need to be returned from the table, but I'm struggling to find a way to order the query results based on the 'code' value from the array. (The table currently contains 14 000 rows and grows each week.)

Table: | id | name | | - | - | | 1 | First item | | 2 | Second item | | 3 | Third item |

Array:

$data = array(
  array('id' => 1, 'code' => 1),
  array('id' => 2, 'code' => 10),
  array('id' => 3, 'code' => 3),
);

I am currently using the following query to retrieve the items that are returned in the array, so the only thing that's missing is the order.

$queryIds = (new Query())
->select(['id', 'name'])
->from('items')
->where(['in', 'id', $items['ids']])
->pairs();

score 0 · Accepted Answer · answered Feb 14 '22 at 17:39

I'd use a derived table subquery for the array of id's and codes.

Here's what I mean in SQL.

SELECT items.id, items.name
FROM items
JOIN (
 SELECT 1 AS id, 1 AS code
 UNION SELECT 2, 10
 UNION SELECT 3, 3
) AS a ON items.id = a.id
ORDER BY a.code

I don't use Yii query builder, but based on the documentation I see it supports union and derived tables.

Order mySQL query (yii db query) based on array values

1 Answers1