0

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.

2

2 Answers 2

1

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
3
  • Thanks for the nomenclature! I was having a really hard time finding information about this approach online. This has opened up a whole world of research for me now. How well do you think something like this may perform at scale i.e. millions of records and thousands of joined fields? Nov 13, 2020 at 15:43
  • @PiranhaGeorge Usually it performs bad :) and if you are worrying about that you will probably end up with creation of additional transformation to flat tables (or something similar to solution suggested by nbk) EAV is a kind of anti-pattern for relational database. Please see this answer - stackoverflow.com/questions/2224234/… Nov 13, 2020 at 15:58
  • Yeah, I'm seeing a lot about the performance issues in my research. I actually previously tried to implement this part of my application with MongoDB. The problem is I need to allow end-users to generate queries for this combined EAV data and regular relational columns where referential integrity is a must. This is infrequently changing data that will be cached, but I don't want to rely on caching alone. I guess more research is in order :-) Nov 13, 2020 at 16:14
1

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

3
  • Thanks for the interesting approach! I don't think I'll need the more flexible version as I'll be programmatically generating the query with all the field information to hand. I'm interested to know if selecting the fields like this offers any performance advantages over separate joins as per @NikitaSerbskiy answer. Do you have any thoughts on this? Nov 13, 2020 at 15:48
  • as you have already a sizeable amount on rows run both aproaches. i think mine is faster but it is hhrd to tell
    – nbk
    Nov 13, 2020 at 15:59
  • Will do. I'm setting up a test bench ATM. Nov 13, 2020 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.