Order by column with specific adjacent value

Apologies for the vague title. I wasn’t really sure how to describe this.

I have the following tables:

items:

id | name
---|----------
1  | first-item
2  | second-item
3  | third-item

fields:

id | name
---|------------
1  | title
2  | description
3  | available

values:

id | item_id | field_id | value (json)
---|---------|----------|-------------------------
1  | 1       | 1        | "First Item"
2  | 1       | 2        | "This is the first item"
3  | 1       | 3        | 1
4  | 2       | 1        | "Second Item"
5  | 2       | 2        | "This is the second item"
6  | 2       | 3        | 1
7  | 3       | 1        | "Third Item"
8  | 3       | 2        | "This is the third item"
9  | 3       | 3        | 0

Here’s an example query with the order by represented as a select to try to illustrate the desired logic :

select `items`.*
from `items`
         left join `values` on `items`.`id` = `values`.`item_id`
         left join `fields` on `values`.`field_id` = `fields`.`id`
where (`fields`.`name` = 'available' and `values`.`value` = 1)
group by `items`.`id`
order by (select `values`.`value` from 'values' where `fields`.`name` = 'title') desc

What I’m trying to do it order the items by values.value for one or more specific fields. Hopefully that makes sense?

The end result should look something like this:

id | name
---|----------
2  | second-item
1  | first-item

I’m really not sure where to start with this and searching has yet to yield any leads. Any help would be greatly appreciated.

You are using EAV-model and you need to join values table to each record several times:

SELECT i.*
    ,v2.value
FROM items AS i
LEFT JOIN values AS v1 ON i.id = v1.item_id AND v1.field_id = (SELECT id FROM fields WHERE name = 'available')
LEFT JOIN values AS v2 ON i.id = v2.item_id AND v2.field_id = (SELECT id FROM fields WHERE name = 'title')
WHERE v1.value = 1
ORDER BY v2.value DESC

Basically Json makes the things, a bit more complicated, but not that much, but as you noticed, i don’t think that it is necessary.
My approach as you need the data, is to use a pivot table.

This is actually flexible as it uses all, independent which are needed.

At the end is the query without the flexibility of automation. But then you have manually have to select the item you want.

Another approach is to transfor the values data into a json, bu then you need to extract all vital parts again. It wouldn’t look that much prettier

CREATE TABLE items (
  `id` INTEGER,
  `name` VARCHAR(11)
);

INSERT INTO items
  (`id`, `name`)
VALUES
  ('1', 'first-item'),
  ('2', 'second-item'),
  ('3', 'third-item');

CREATE TABLE fields (
  `id` INTEGER,
  `name` VARCHAR(11)
);

INSERT INTO fields
  (`id`, `name`)
VALUES
  ('1', 'title'),
  ('2', 'description'),
  ('3', 'available');

CREATE TABLE values1 (
  `id` INTEGER,
  `item_id` INTEGER,
  `field_id` INTEGER,
  `value` JSON
);

INSERT INTO values1
  (`id`, `item_id`, `field_id`, `value`)
VALUES
  ('1', '1', '1', '"First Item"')
  ,
  ('2', '1', '2', '"This is the first item"'),
  ('3', '1', '3', '"1"'),
  ('4', '2', '1', '"Second Item"'),
  ('5', '2', '2', '"This is the second item"'),
  ('6', '2', '3', '"1"'),
  ('7', '3', '1', '"Third Item"'),
  ('8', '3', '2', '"This is the third item"'),
  ('9', '3', '3', '"0"');

    SELECT 
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(CASE v.`field_id` WHEN "',
         f.`id`,
          '" THEN v.`value` ELSE NULL END) AS `',
          f.`name`), '`'      
        
        ORDER BY f.`id`
      ) 
      INTO @sql
    FROM `items` i
         left join 
         `values1` v 
         on i.`id` = v.`item_id`
         left join `fields` f on v.`field_id` = f.`id`;

SET @sql = CONCAT('select MAX(i.`name`), ',@sql,' from `items` i
         left join 
         `values1` v 
         on i.`id` = v.`item_id`
         left join `fields` f on v.`field_id` = f.`id`
group by i.`id`
HAVING `available` + 0 = 1
ORDER BY title DESC');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MAX(i.`name`) | title         | description               | available
:------------ | :------------ | :------------------------ | :--------
second-item   | "Second Item" | "This is the second item" | "1"      
first-item    | "First Item"  | "This is the first item"  | "1"      

select 
MAX(i.`name`)
, MAX(CASE v.`field_id` WHEN "1" THEN v.`value` ELSE NULL END) AS `title`
,MAX(CASE v.`field_id` WHEN "2" THEN v.`value` ELSE NULL END) AS `description`
,MAX(CASE v.`field_id` WHEN "3" THEN v.`value` ELSE NULL END) AS `available` 
from `items` i
         LEFT  join 
         `values1` v 
         on i.`id` = v.`item_id`
         LEFT join `fields` f on v.`field_id` = f.`id`
group by i.`id`
HAVING `available` + 0 = 1
ORDER BY `title` DESC

MAX(i.`name`) | title         | description               | available
:------------ | :------------ | :------------------------ | :--------
second-item   | "Second Item" | "This is the second item" | "1"      
first-item    | "First Item"  | "This is the first item"  | "1"      

db<>fiddle here