Как отфильтровать результаты поиска в PostgresSQL?

Question

[coderok]

Добрый день!

Уже несколько дней не могу решить задачу с поиском.

Имеются такие таблицы:

contact table

id |last_modified       |date_create         |
---|--------------------|--------------------|
1  |2016-06-03 11:59:20 |2016-06-03 11:59:20 |
2  |2016-06-03 11:59:20 |2016-06-03 11:59:20 |
3  |2016-06-03 11:59:20 |2016-06-03 11:59:20 |

custom_field table

id |element_id |element_type |enum_code  |
---|-----------|-------------|-----------|
1  |1          |0            |First Name |
2  |1          |0            |Last Name  |
3  |1          |0            |Company    |
4  |1          |0            |Phone      |
5  |1          |0            |Email      |
6  |2          |0            |First Name |
7  |2          |0            |Last Name  |
8  |2          |0            |Company    |
9  |2          |0            |Phone      |
10 |2          |0            |Email      |
11 |3          |0            |First Name |
12 |3          |0            |Last Name  |
13 |3          |0            |Company    |
14 |3          |0            |Phone      |
15 |3          |0            |Email      |

custom_field_value table

id |custom_field_id |enum_value_code |value                     |last_modified       |
---|----------------|----------------|--------------------------|--------------------|
1  |1               |First Name      |John                      |2016-06-03 11:59:20 |
2  |2               |Last Name       |Galt                      |2016-06-03 11:59:20 |
3  |3               |Company         |XYZ Inc                   |2016-06-03 11:59:20 |
4  |4               |Work            |[phone]            |2016-06-03 11:59:20 |
5  |4               |Mobile          |[phone]            |2016-06-03 11:59:20 |
6  |4               |Home            |[phone]            |2016-06-03 11:59:20 |
7  |5               |Work            |theTalentedtest@xyz       |2016-06-03 11:59:20 |
8  |5               |Home            |theYoungtest@xyz          |2016-06-03 11:59:20 |
10 |7               |Last Name       |Doe                       |2016-06-03 11:59:20 |
11 |8               |Company         |Dream Company             |2016-06-03 11:59:20 |
12 |9               |Work            |[phone]            |2016-06-03 11:59:20 |
13 |9               |Mobile          |[phone]            |2016-06-03 11:59:20 |
14 |9               |Home            |[phone]            |2016-06-03 11:59:20 |
15 |10              |Work            |veryWrongtest@dream       |2016-06-03 11:59:20 |
16 |10              |Home            |veryStereotypedtest@dream |2016-06-03 11:59:20 |
17 |11              |First Name      |Suzy                      |2016-06-03 11:59:20 |
18 |12              |Last Name       |Corn                      |2016-06-03 11:59:20 |
19 |13              |Company         |PipeIt                    |2016-06-03 11:59:20 |
20 |14              |Work            |[phone]            |2016-06-03 11:59:20 |
21 |14              |Mobile          |[phone]            |2016-06-03 11:59:21 |
22 |14              |Home            |[phone]            |2016-06-03 11:59:21 |
23 |15              |Work            |Wigglysuper@pipeit        |2016-06-03 11:59:21 |
24 |15              |Home            |[e-mail]     |2016-06-03 11:59:21 |
9  |6               |First Name      |John                      |2016-06-03 11:59:20 |

У контакта имеются поля, которые находятся в таблице custom_field. Значения этих полей находятся в таблице custom_field_value.

До этого поиск работал через ILIKE '%some text%' и в результате я получал контакт и все его поля.

Проблема этого решения, что мы ищем совпадение по одному полю.
Если я, например, ищу по 'John', то найду два контакта. Но если я буду искать по 'John XYZ Inc', то контакт не будет найден.
Поэтому я немного переделал запрос на ILIKE ANY(array['%John%', '%XYZ Inc%'])

Этот запрос также вернет два контакта, но мне нужен только тот контакт у которого в полях есть и 'John' и 'XYZ Inc'.

То есть как-то отфильтровать результаты поиска, но я не знаю как это сделать через SQL.

Скрипт для создания таблиц можно взять по ссылке: Скрипт

Запрос:

SELECT
   c.id,
   c.last_modified,
   c.date_create,
   cf.id as cf_id,
   cf.enum_code,
   cfv.id as cfv_id,
   cfv.enum_value_code,
   cfv.value,
   cfv.last_modified as cfv_last_modified   
FROM
   contact c   
LEFT JOIN
   custom_field cf 
      ON (
         cf.element_id=c.id 
         AND cf.element_type=0
      )  
LEFT JOIN
   custom_field_value cfv 
      ON cfv.custom_field_id=cf.id,
   (SELECT
      DISTINCT sub_cf.element_id    
   FROM
      custom_field_value sub_cfv    
   INNER JOIN
      custom_field sub_cf 
         ON sub_cfv.custom_field_id = sub_cf.id    
   INNER JOIN
      contact sub_c 
         ON sub_c.id = sub_cf.element_id    
   WHERE
      sub_cf.element_type=0 
      AND sub_cfv.value ILIKE ANY(ARRAY['%John%', '%XYZ%'])    
   ORDER BY
      sub_cf.element_id DESC OFFSET 0 LIMIT 50) AS sub_search  
WHERE
   sub_search.element_id=c.id 
ORDER BY
   c.last_modified DESC;

Comments

[SharuPoNemnogu]

покажите свой запрос

[coderok]

SharuPoNemnogu: обновил вопрос

[SharuPoNemnogu]

SELECT
      sub_cf.element_id    
   FROM
      custom_field_value sub_cfv    
   INNER JOIN
      custom_field sub_cf 
         ON sub_cfv.custom_field_id = sub_cf.id    
   INNER JOIN
      contact sub_c 
         ON sub_c.id = sub_cf.element_id    
   WHERE
      sub_cf.element_type=0 
      AND sub_cfv.value ILIKE ANY(ARRAY['%John%', '%XYZ%'])
      GROUP BY sub_cf.element_id
      HAVING COUNT(sub_cfv.value) > 1

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