Помогите составить запрос к БД для вывода по связям

Question

Вопрос следующий, как можно выбрать данные по связям в формате вложенных объектов (или коллекций). Есть три таблицы:

Таблица 'category' (там например есть растения, кустарники, цветы)
Таблица 'object' (тут информация о конкретном объекте, который может быть 1 из таблицы категории - например "Розы"-> категория цветы)
Таблица 'variations' (это разные вариации объекта, например Розовые розы -> ID в таблице 'object' с названием "Розы", Желтые розы, и т.д.)

PS: надеюсь понятно, что у каждого 'object' может быть только 1 'categoory' и много 'variations'.

В чём сложность. Не могу составить запрос, в котором бы выходил результат примерно такого формата:

[0]=>{
  "name"=>"Розы"
  "category"=>"Цветы"
  [variations]=>{
    [0]=>"Розовые розы"
    [1]=>"Жёлтые розы"
    [2]=>"Красные розы"
 }
}

Я написал запрос с INNER JOIN и у меня выводится столько же повторных запросов сколько 'variations' т.е. примерно так

 [0]=>
{
      "name"=>"Розы"
      "category"=>"Цветы"
      "variations"=>"Розовые розы"
     }
 [1]=>
{
      "name"=>"Розы"
      "category"=>"Цветы"
      "variations"=>"Жёлтые розы"
     }
 [2]=>
{
      "name"=>"Розы"
      "category"=>"Цветы"
      "variations"=>"Красные розы"
     }

Хотелось бы получить 1 набор, и по связи вложенный массив принадлежащий конкретной записи. Подскажите пожалуйста, как это получить? Пишу на чистом PHP с использованием PDO. Подобную задачу раньше пробовал решать на Laravel, там через Relation и Eloquent само так выводилось)

Добавил запрос:

$stmt = $link->prepare("select * from object
          INNER JOIN category ON category.id = object.cat_id
          INNER JOIN variations ON object.id = variations.obj_id
          ");

Как то так...

Answer 1

Можно применить JSON_ARRAYAGG(), но получать одно из полей в виде JSON массива нет смысла. Если нужно сделать JSON из всего массива, всё равно придётся обходить в цикле и разбирать его, так что проще склеить через запятую а потом разбить. А можно обойти циклом вообще без группировки. С группировкой так:

$stmt = $link->prepare("SELECT o.name, c.category, GROUP_CONCAT(v.variations) variations
  FROM object o
  INNER JOIN category c ON c.id = o.cat_id
  INNER JOIN variations v ON v.obj_id=o.id
  GROUP BY v.variations");

$res=$stmt->exec();

$result=[];
foreach($res as $row){
    $row['variations']=explode(',', $row['variations']);
    $result[]=$row;
}

//Флаги для красоты, потом лучше убрать,
//особенно JSON_UNESCAPE_UNICODE, это нестандартный формат
$result=JSON_encode($result, JSON_UNESCAPE_UNICODE+JSON_PRETTY_PRINT);
echo $result;

без группировки так:

$stmt = $link->prepare("SELECT o.name, c.category, GROUP_CONCAT(v.variations) variations
  FROM object o
  INNER JOIN category c ON c.id = o.cat_id
  INNER JOIN variations v ON v.obj_id=o.id");

$res=$stmt->exec();

$result=[];
foreach($res as $row){
    $result[$row['name']]['category']=$row['category'];
    $result[$row['name']]['variations'][]=$row['variations'];
}
//Можно ключи в принципе оставить, но если не нужны
//$result=array_values($result);
$result=JSON_encode($result, JSON_UNESCAPE_UNICODE+JSON_PRETTY_PRINT);
echo $result;

Answer 2

Соединение с таблицей "variations" добавит лишние строки. Поэтому можно подзапросом собрать список и напрямую включить в результаты.

$query = " 
  select o.id,o.object_name,c.category_name
    ,(select group_concat(variation_name) 
      from variations v where v.object_id=o.id) variations
from objects o 
left join category c on o.category_id=c.id;";


$stmt = $pdo->prepare($query);
$result=$stmt->execute();

$rows = $stmt->fetch(PDO::FETCH_ASSOC);

while ($rows){
  $target=array();
  $id=$rows["id"];
  $target[$id]=array();
  $target[$id]["id"]=$rows["id"];
  $target[$id]["name"]=$rows["object_name"];
  $target[$id]["category"]=$rows["category_name"];
  $target[$id]["variations"]=explode(",",$rows["variations"]);
  print_r($target[$id]);
  $rows = $stmt->fetch(PDO::FETCH_ASSOC);
};

Получится

Result: 
Array
(
    [id] => 1
    [name] => Розы
    [category] => Цветы
    [variations] => Array
        (
            [0] => Розовые розы
            [1] => Желтые розы
            [2] => Красные розы
        )

)
Array
(
    [id] => 2
    [name] => Астры
    [category] => Цветы
    [variations] => Array
        (
            [0] => Красные астры
            [1] => Белые астры
        )

)

Если же нужно выбрать всего один объект, можно сделать запрос так:

$query = " 
  select o.id,o.object_name
    ,(select category_name 
      from category c where o.category_id=c.id) category
    ,(select group_concat(variation_name) 
      from variations v where v.object_id=o.id) variations
from objects o
where o.id=?;

Пример PHP

Пример в MySQL

id	category_name
1	Цветы

id	category_id	object_name
1	1	Розы
2	1	Астры

id	object_id	variation_name
1	1	Розовые розы
2	1	Желтые розы
3	1	Красные розы
4	2	Красные астры
5	2	Белые астры

select object_name,category_name
  ,(select group_concat(variation_name) from variations v where v.object_id=o.id) variations
from objects o
left join category c on o.category_id=c.id

object_name	category_name	variations
Розы	Цветы	Розовые розы,Желтые розы,Красные розы
Астры	Цветы	Красные астры,Белые астры

fiddle

БЛОГ НА HUSL

Помогите составить запрос к БД для вывода по связям

Ответы (2 шт):