К примеру вот так. категории-подкатегории-работы, можно так же работы-категории-подкатегории, можно в процедурном виде
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
<?php
class Works
{
private static $worksTree = null;
public static function getWorksTree($force = false)
{
if (self::worksTree === null || $force) {
self::worksTree = [];
$sql = "SELECT c.id AS categoryId,
c.title AS categoryTite,
t.id AS categoryTypeId ,
t.title AS categoryTypeTitle,
w.id AS workId,
w.title AS workTitle
FROM works w
INNER JOIN categories c ON c.id = w.categoryId
INNER JOIN categoryTypes t ON t.id = c.categoryTypeId";
$worksTree = DI::db->findBySql($sql)->all();
foreach ($worksTree as $tree) {
self::worksTree[$tree['categoryId']]['title'] = $tree['categoryTite'];
self::worksTree[$tree['categoryId']]['types'][$tree['categoryTypeId']]['title'] = $tree['categoryTypeTitle'];
self::worksTree[$tree['categoryId']]['types'][$tree['categoryTypeId']]['works'] = [
'id' => $tree['workId'],
'title' => $tree['workTitle']
];
}
}
return self::$worksTree;
}
}
print_r(Works::getWorksTree());
//должно получится что-то вроде этого
//Array()
//[1] => Array()
// ['title'] => 'Category One'
// ['types'] => Array()
// [1] => Array()
// ['title'] => 'Type One'
// ['works'] => Array()
// [0] => Array()
// ['id'] => 1
// ['title'] => 'Work One'
// [1] ...
// [2] ....
//[2] ...
