Блог

Учитывая следующие таблицы:

родительский:

  ---- 
| id |
 ---- 
|  1 |
|  2 |
|  3 |
 ---- 
  

свойство:

  ---- ----------- ------ ------- 
| id | parent_id | name | value |
 ---- ----------- ------ ------- 
|  1 |         1 | aaa  |     5 |
|  2 |         1 | bbb  |     2 |
|  3 |         1 | ccc  |     9 |
|  4 |         2 | aaa  |     8 |
|  5 |         2 | ccc  |     9 |
|  6 |         3 | bbb  |    10 |
|  7 |         3 | ddd  |     1 |
|  8 |         3 | ccc  |     9 |
 ---- ----------- ------ ------- 
  

В общем случае запросы имеют следующий вид:
Выберите родительские идентификаторы, если:

 * Have a property called 'xxx' with value 'yyy'                                         <---|
* and/or                                                                                    |
* Have a property called 'xxx' with value NOT equal 'yyy' OR do not have 'xxx' proprty. <---|
* and/or                                                                                    |
* Repeat -----------------------------------------------------------------------------------|
  

Так, например, запрос может выглядеть следующим образом:
Выберите родительские идентификаторы, если:

• Имеют свойство с именем ‘bbb’ и его значение ! = 2 ИЛИ не имеют свойства ‘bbb’.
• (и)
• Есть свойство с именем ‘ccc’ и его значение == 9.

В приведенном выше примере результатом запроса будет:

• 2 (не имеет свойства ‘bbb’ и имеет ‘ccc’ == 9).
• 3 (Имеет свойство ‘bbb’ со значением != 2 и имеет ‘ccc’ == 9).

В настоящее время я делаю что-то вроде этого:

 select * from parent where
    -- use "not in" and invert check for "NOT equal or no property queries"
    id not in (select parent_id from property where name = 'bbb' and value = 2)
    and id in (select parent_id from property where name = 'ccc' and value = 9)
  

Мне было интересно, есть ли лучший подход, поскольку это может вырасти до слишком большого числа select внутри where предложения.

Вы можете объединить таблицы и использовать условную агрегацию:

 select p.id
from parent p inner join property t
on t.parent_id = p.id
group by p.id
having sum(t.name = 'bbb' and t.value = 2) = 0
   and sum(t.name = 'ccc' and t.value = 9) > 0
  

Это не обязательно с точки зрения производительности лучше, чем ваше решение, но я думаю, что оно более читабельно и лучше масштабируется, поэтому вы можете добавить больше условий.

Существует также решение с EXISTS / NOT EXISTS :

 select p.* from parent p
where not exists (select 1 from  property t where t.parent_id = p.id and t.name = 'bbb' and t.value = 2)
  and exists (select 1 from  property t where t.parent_id = p.id and t.name = 'ccc' and t.value = 9)
  

Смотрите демонстрацию.
Результаты:

 > | id |
> | -: |
> |  2 |
> |  3 |
  

Ответ Forpas — corect. Однако это можно упростить и улучшить двумя способами.

Во-первых, join для запроса не требуется. Во-вторых, SQLite поддерживает нотацию кортежей для упрощения логики. Таким образом, логика может быть выражена как:

 select parent_id
from property p
group by parent_id
having sum( (name, value) = ('ccc', 9) ) > 0 and
       sum( (name, value) = ('bbb', 2) ) = 0;
  

Вот SQL-скрипка.