Sort By Array

Recently a question was posted on Stack Overflow requesting a method for sorting items in an array based on multiple criteria, but unrelated to any numeric or lexical characteristics. Instead, the sort was based upon other characteristics (in this case, knowing that an expanded font is heavier than a standard font and a standard font is heavier than a condensed font.  The ultimate goal was to sort a list of fonts by weight.

The question reminded me of a similar problem I had a attempting to return rows from PostgreSQL sorted in a specific order provided with the query.  The following example is a little arbitrary, but it demonstrates the basic principle.

If I had a list of fruits, I might want to rank them from my most favorite to least.  Let's begin by generating our list of fruits.

CREATE TABLE fruit ( "id" serial NOT NULL, "name" text NOT NULL, PRIMARY KEY ("id") ); 
INSERT INTO fruit (name) VALUES ('Apple');
INSERT INTO fruit (name) VALUES ('Orange'); 
INSERT INTO fruit (name) VALUES ('Pear');

In a typical CRUD app, you might have an additional table called my_fruit which joins to fruit and stores my personal ranking for each fruit.  But what if we could simply have an additional column on our user_preferences table called fruits and store our list there?  We might put them in an array like this:

UPDATE user_preferences 
	set fruits=ARRAY['Pear','Orange','Apple'] 
WHERE fk_user=1;

At this point we can query our fruits table to return a list of fruits in the order and preference defined in our user_preferences table.

		p.fruits[s.position] AS name 
	FROM user_preferences p, 
	generate_series(1, array_length(p.fruits, 1)) AS s(position) 
	WHERE p.fk_user = 1 
) x 
JOIN fruit ON ( 
ORDER BY x.position asc;

Here are the results:

| id | name   | 
| 3  | Pear   |
| 2  | Orange |
| 1  | Apple  |

While in this case, it might have been simpler to just return the list from user_preferences, in a real-world scenario there would likely be additional information in our fruits table, perhaps the origin, a description, and a list of varieties.

PHP Implementation

After revisiting the old PostgreSQL implementation, I set out to write a PHP function that allowed multiple criteria arrays.  The following is an example usage of sort_by_array(), which sorts a list of dogs and cats from biggest to smallest.

$animals = ['big cat', 'small dog', 'big dog', 'small cat'];
$sort_criteria = [['dog','cat'], ['big','small']];
sort_by_array($animals, $sort_criteria);

Resulting output:

    [0] => big dog 
    [1] => small dog 
    [2] => big cat 
    [3] => small cat 

The code is available here.