Как составить сложную выборку из двух таблиц?

Question

[rygat]

Есть две таблицы

table1
+----+----------+--------+-------------+----------+----------+----------+------------+
| id |  address | office |     fio     | counter1 | counter2 | counter3 |    date    |
+----+----------+--------+-------------+----------+----------+----------+------------+
| 1  | Ленина 3 |   65   | Иванов И.И. |   1678   |    127   |    246   | 01.01.2024 |
| 2  | Ленина 4 |   18   | Павлов П.П. |   327    |    64    |    346   | 03.01.2024 |
+----+----------+--------+-------------+----------+----------+----------+------------+

table2
+----------+--------+----------+---------+
|  address | office | counter  |   num   |
+----------+--------+----------+---------+
| Ленина 3 |   65   | counter1 | c000001 |
| Ленина 3 |   65   | counter2 | c000002 |
| Ленина 3 |   65   | counter3 | c000003 |
| Ленина 4 |   18   | counter1 | c000004 |
| Ленина 4 |   18   | counter2 | c000005 |
| Ленина 4 |   18   | counter3 | c000006 |
+----------+--------+----------+---------+

Нужно выгрузит их в файл csv в таком виде

+----------+--------+-------------+----------+-------+---------+------------+
|  address | office |     fio     |  counter | value |   num   |    date    |
+----------+--------+-------------+----------+-------+---------+------------+
| Ленина 3 |   65   | Иванов И.И. | counter1 | 1678  | c000001 | 01.01.2024 |
| Ленина 3 |   65   | Иванов И.И. | counter2 | 127   | c000002 | 01.01.2024 |
| Ленина 3 |   65   | Иванов И.И. | counter3 | 246   | c000003 | 01.01.2024 |
| Ленина 4 |   18   | Павлов П.П. | counter2 | 327   | c000004 | 03.01.2024 |
| Ленина 4 |   18   | Павлов П.П. | counter2 | 64    | c000005 | 03.01.2024 |
| Ленина 4 |   18   | Павлов П.П. | counter2 | 346   | c000006 | 03.01.2024 |
+----------+--------+-------------+----------+-------+---------+------------+

Таблицы для примера сокращены, количество столбцов и counter'ов в три раза больше.
Сейчас я загружаю первую таблицу и на php в цикле все собираю каждый раз подгружая данные из второй таблицы. Но это очень долго получается и у пользователя закрадывается сомнение, не завис ли процесс.
Я пробовал объединить их slq запросом

SELECT t1.id, t1.address, t1.office, t1.fio, t1.counter, t1.value, table2.num, t1.date from (
SELECT id, address, office, date, fio, 'counter1' as counter, counter1 as 'value' FROM table1
UNION ALL
SELECT id, address, office, date, fio, 'counter2' as counter, counter2 as value FROM table1
UNION ALL
SELECT id, address, office, date, fio, 'counter3' as counter, counter3 as value FROM table1
) t1
LEFT JOIN table2 ON t1.address = table2.address AND t1.office = table2.office AND t1.counter = table2.scetchik
WHERE (t1.date BETWEEN '2021-02-01' AND '2021-02-25') AND t1.value != 0;

но база также долго думает, да и как правильно поместить этот запрос в laravel.
Может есть у кого элегантное решение задачи?

Comments

[Akina]

Тест-данные нужно давать в виде CREATE TABLE + INSERT INTO, а не (только) таблицей.

количество столбцов и counter'ов в три раза больше.

Ну так нормализуйте данные-то. Что ж самому себе такой геморрой создавать?

[rygat]

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

[Akina]

rygat, будь у тебя нормальное приложение прямого доступа, я бы предложил дополнительно создать нормализованную структуру, копировать туда данные триггерами, и обращаться за итогами к нормализованным таблицам... но, блин, Ларавель такое не умеет емнип совсем.

Answer 1

[Akina]

SELECT address, 
       office, 
       t1.fio, 
       t2.counter, 
       CASE t2.counter
           WHEN 'counter1' THEN t1.counter1
           WHEN 'counter2' THEN t1.counter2
           WHEN 'counter3' THEN t1.counter3
           END value,
       t2.num,
       t1.date       
FROM table1 t1
JOIN table2 t2 USING (address, office)

Comments

[rygat]

В этом запросе есть одна проблема, если t2 отсутствуют какие то из counter'ов, то они не выводятся, вроде LEFT JOIN должен в этом случае подставлять NULL, но он не работает.

[Akina]

rygat, Это проблема отсутствия тестовых данных, о чём я давно написал в комментарии к вопросу. И именно там должны быть учтены все подобного рода "особенности" исходного набора данных.

Answer 2

[Dmitry Bay]

SELECT t2.address, t2. office, t1.fio, t2.counter, t1.value (только этого поля нет у вас)
FROM table2 t2
LEFT JOIN table1 ON t1.office=t2.office

Comments

[rygat]

t1.value формируется здесь: SELECT id, address, office, date, fio, 'counter1' as counter, counter1 as 'value' FROM table1
Основной таблицей является table1, нужно взять нужные строки из table1 и присоединить к ней table2, а у вас получается наоборот.

[Dmitry Bay]

Судя по результату, у вас основная это t2 и к ней left join прикрепляете t1.

counter1 as 'value'

Вам тут проще не делать логику на уровне базы данных, а забрать
counter_name, counter1 ... counter3

И в момент рендеринга на уровне кода, отдавать необходимое значение. Или написать сервис, который будет отдавать сразу value.

[Dmitry Bay]

UPD.

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