Select from subquery where multiple rows

Question

I'm having some trouble with a dynamic SQL search query where i would like to be able to find all objects that have any of the fields I'm searching for. Below is the datastructure.

objectregister                      fieldvalue
| id |  name   |         | id |  objid  | fieldid | value  (illustration)  |
+----+---------+         +----+---------+---------+------------------------+
| 1  |  CUBE   |         | 1  |    1    |    12   |   4    (BLUE)          |
| 2  |  SQUARE |         | 2  |    2    |    12   |   4    (BLUE)          |
                         | 3  |    1    |    22   |   27   (SMALL)         |
                         | 4  |    2    |    22   |   9    (BIG)           |

Test Bench with DB structure:

CREATE TABLE IF NOT EXISTS `objectregister` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(1024) COLLATE utf8_swedish_ci NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `objectregister` (`id`, `name` ) VALUES
(1, 'CUBE'),
(2, 'SQUARE');

CREATE TABLE IF NOT EXISTS `fieldvalue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `objid` int(11) NOT NULL,
  `fieldid` int(11) NOT NULL,
  `value` varchar(2048) COLLATE utf8_swedish_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
);

INSERT INTO `fieldvalue` (`id`, `objid`, `fieldid`, `value`) VALUES
(1, 1, 12, 4),
(2, 2, 12, 4),
(3, 1, 22, 27),
(4, 2, 22, 9);


SELECT `id`, `name` FROM `objectregister` WHERE id IN 
(
    SELECT * FROM 
    (
        SELECT `objid` 
        FROM `fieldvalue` 
        WHERE 1  AND ( (fieldvalue.fieldid = '12' AND fieldvalue.value = '4')  OR (fieldvalue.fieldid = '22' AND fieldvalue.value = '27')  ) 
        GROUP BY objid 
    ) as subquery
  )

  +----+--------+
  | id | name   |
  +----+--------+
  |  1 | CUBE   |
  |  2 | SQUARE |
  +----+--------+

https://rextester.com/XTR72354

Example:

I would like to find all objects that are blue. In this case: CUBE, SQUARE

I would like to find all object that are blue AND big . In this case: SQUARE

Therefore, I gues I would first need to select all possible objid 's that matches any of the search in a subquery. Get them into one single line so I later can select on WHERE both of them match? But how do I do that? Would i need multiple SUB query unions? GROUP_CONCAT? TEMP table?

This is the current stage of my query as of writing (that would return both rows in subquery, but would need the WHERE on outer query):

SELECT `id`, `name` FROM `objectregister` WHERE id IN 
(
    SELECT * FROM 
    (
        SELECT `objid` 
        FROM `fieldvalue` 
        WHERE 1  AND ( (fieldvalue.fieldid = '12' AND fieldvalue.value = '4')  OR (fieldvalue.fieldid = '22' AND fieldvalue.value = '27')  ) 
        GROUP BY objid 
    ) as subquery
)

Answer 1

• EAV schema has a lot of problems.

• For fieldvalue, get rid of id, instead have PRIMARY KEY(objid, fieldid).

• Also for fieldvalue, have INDEX(fieldid) . (Probably not worth including value since it is too long.)

• Avoid IN ( SELECT ... ); change to JOIN .. ON or EXISTS( SELECT 1 ... )

I would like to find all objects that are blue. In this case: CUBE, SQUARE

SELECT obj.name
    FROM ( SELECT objid
               FROM fieldvalue
               WHERE fieldid = '12'
                 AND `value` = '4'
         ) AS x
    JOIN objectregister AS obj ON x.objid = obj.id;

Alternatively, change the first line to

SELECT GROUP_CONCAT(obj.name)

I would like to find all object that are blue AND big . In this case: SQUARE

SELECT obj.name
    FROM ( SELECT objid FROM fieldvalue WHERE fieldid = '12' AND `value` =  '4' ) AS x
    JOIN ( SELECT objid FROM fieldvalue WHERE fieldid = '22' AND `value` = '27' ) AS y
                               USING(objid)
    JOIN objectregister AS obj ON x.objid = obj.id;

The philosophy here might be "Start with what you know; work toward what you need to know" instead of "Let's check every objid to see which ones apply".

Answer 2

Similarly...

SELECT o.*
  FROM objectregister o 
  JOIN fieldvalue v 
    ON v.objid = o.id 
 WHERE value IN (4,9) 
 GROUP 
    BY o.id 
HAVING COUNT(DISTINCT value) = 2;

If values have different meanings in different contexts, then you also need the fieldids. A short (but inefficient way of writing that is WHERE (fieldid,value) IN((12,4),(etc))

Answer 3

One option is to use exists:

select r.*
from objectregister r
where 
    exists (
        select 1 from fieldvalue f where f.objid = r.id and f.fieldid = 12 and f.value = 4
    ) and exists (
        select 1 from fieldvalue f where f.objid = r.id and f.fieldid = 22 and f.value = 27
    )

With an index on fieldvalue(objid, fieldid, value), this should be an efficient solution.

You can join, aggregate and filter with a having clause:

select r.id, r.name
from objectregister r
inner join fieldvalue f on f.objid = r.id
group by r.id, r.name
having max(f.fieldid = 12 and f.value = 4) = 1 and max(f.fieldid = 22 and f.value = 27) = 1