Как построить SQL-запрос из списка выбранных полей?

Question

Как построить SQL-запрос из списка выбранных полей?

У меня есть приложение, которое программируется с использованием PHP и MySQL для серверной части. Большинство моих таблиц — это стандартные таблицы MySQL Innodb. Кроме того, у меня есть функция, где администратор может добавлять пользовательские атрибуты, используя Модель EAV

Мне нужно создать функцию в приложении, где пользователь может создать собственное представление. По сути, я хочу дать пользователю список всех доступных полей. Пользователь может выбрать, какие поля он / она хочет видеть на своем экране, и система создаст запрос с соответствующими СОЕДИНЕНИЯМИ и отобразит результаты.

Например, если пользователь хочет отобразить поля 1, 2, 3, 4, 5, 6, 7, 8, 9. Мне нужно будет выполнить следующие шаги

Я проверю, к какой таблице относится каждое поле.
Выясните, какое имя таблицы входит в FROM часть запроса
Выясните, какое имя таблицы входит в JOIN часть запроса, если это необходимо. и определить, когда поля идут в ON пункт из JOIN заявление.

Первый шаг довольно восточный, так как эта информация хранится в entity_definitions таблица «определена ниже»

Второй шаг также довольно прост, поскольку я знаю, какая таблица является базовой, и я могу ее заполнить (т.е. accounts).

«При необходимости» третий шаг довольно сложный, и я не уверен, как этого добиться. Как узнать, какую таблицу таблицы использовать для объединения и какие имена столбцов использовать для ON статья?

Я попытался решить проблему, создав таблицу, в которой есть 2 столбца: один столбец для имени поля, а другой — для имени внешнего поля. Однако я все еще не могу построить запрос, просто посмотрев на выбранные имена полей.

Чтобы отслеживать определение таблицы / атрибутов, я создал таблицу (т.е. entity_definitions), который имеет список всех столбцов.

CREATE TABLE entity_definitions (
entity_id int(11) unsigned NOT NULL AUTO_INCREMENT,
display_name varchar(255) NOT NULL,
entity_key varchar(60) NOT NULL,
entity_type enum('lookup','Integer','text','datetime','date') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
object_id int(11) unsigned NOT NULL,
PRIMARY KEY (entity_id),
KEY object_id (object_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

object_id ссылаться на имя таблицы, к которой оно принадлежит, в entity_objects Таблица.

CREATE TABLE entity_objects (
object_id int(11) unsigned NOT NULL AUTO_INCREMENT,
object_name varchar(60) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
object_description varchar(255) DEFAULT NULL,
PRIMARY KEY (object_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Вот ссылка jFiddle у него есть таблицы и некоторые данные / запросы, которые я сделал до сих пор.

Вопрос: Как я могу выполнить шаг № 3? лучшие идеи также приветствуются.

1

database entity-attribute-value mysql php query-builder

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Рассмотрим следующее … хотя обратите внимание, что любую дополнительную фильтрацию лучше всего выполнять в таблицах приложения, так как индексы этих таблиц будут недоступны для суперзапроса …

SELECT * from eav_core;
+----+--------+------------+----------+
| id | entity | attribute  | datatype |
+----+--------+------------+----------+
|  1 |      1 | start_date | DATE     |
|  2 |      1 | shift      | INTEGER  |
|  3 |      1 | station1   | VARCHAR  |
|  4 |      1 | station2   | VARCHAR  |
|  5 |      1 | station3   | VARCHAR  |
|  6 |      2 | start_date | DATE     |
|  7 |      2 | shift      | INTEGER  |
|  8 |      2 | station1   | VARCHAR  |
| 11 |      1 | end_date   | date     |
| 12 |      2 | end_date   | date     |
+----+--------+------------+----------+

SELECT * from eav_strings;
+--------+-----------+-------+
| entity | attribute | value | -- value = VARCHAR(50)
+--------+-----------+-------+
|      1 | station1  | Mark  |
|      1 | station2  | John  |
|      1 | station3  | Macy  |
|      2 | station1  | John  |
+--------+-----------+-------+

SELECT * from eav_dates;
+--------+------------+------------+
| entity | attribute  | value      | -- value = DATE
+--------+------------+------------+
|      1 | end_date   | 2015-05-09 |
|      1 | start_date | 2009-04-03 |
|      2 | end_date   | 2015-07-14 |
|      2 | start_date | 2009-04-04 |
+--------+------------+------------+

SELECT * from eav_integers;
+--------+-----------+-------+
| entity | attribute | value | -- value = INT
+--------+-----------+-------+
|      1 | shift     |     1 |
|      2 | shift     |     2 |
+--------+-----------+-------+

SELECT entity
, MAX(CASE WHEN attribute = 'start_date' THEN value END) start_date
, MAX(CASE WHEN attribute = 'end_date' THEN value END) end_date
, MAX(CASE WHEN attribute = 'station1' THEN value END) station1
, MAX(CASE WHEN attribute = 'station2' THEN value END) station2
, MAX(CASE WHEN attribute = 'station3' THEN value END) station3
, MAX(CASE WHEN attribute = 'shift' THEN value END) shift
FROM
(
SELECT x.entity
, x.attribute
, y.value
FROM eav_core x
JOIN eav_dates y
ON y.entity = x.entity
AND y.attribute = x.attribute
WHERE x.datatype = 'date'
UNION ALL
SELECT x.entity
, x.attribute
, y.value
FROM eav_core x
JOIN eav_integers y
ON y.entity = x.entity
AND y.attribute = x.attribute
WHERE x.datatype = 'integer'
UNION ALL
SELECT x.entity
, x.attribute
, y.value
FROM eav_core x
JOIN eav_strings y
ON y.entity = x.entity
AND y.attribute = x.attribute
WHERE x.datatype = 'varchar'
) n
GROUP
BY entity;
+--------+------------+------------+----------+----------+----------+-------+
| entity | start_date | end_date   | station1 | station2 | station3 | shift |
+--------+------------+------------+----------+----------+----------+-------+
|      1 | 2009-04-03 | 2015-05-09 | Mark     | John     | Macy     | 1     |
|      2 | 2009-04-04 | 2015-07-14 | John     | NULL     | NULL     | 2     |
+--------+------------+------------+----------+----------+----------+-------+

0