I have tested my SQL query through DBeaver, and it executes successfully without any errors. However, when I try to create a Custom View (v2025) with the same query, I constantly receive an error, and the Custom View is not created.
Could you please assist me with this issue?
Why does the query work in DBeaver but fails when creating a Custom View?
Is there any limitation or specific syntax requirement for creating Custom Views in the v2025 version?
Thank you in advance for your support.
SELECT DISTINCT
CONCAT(
CASE WHEN l1_trans.value IS NOT NULL THEN l1_trans.value ELSE 'Без названия' END,
' -> ',
CASE WHEN l2_trans.value IS NOT NULL THEN l2_trans.value ELSE 'Без названия' END,
' -> ',
CASE WHEN l3_trans.value IS NOT NULL THEN l3_trans.value ELSE 'Без названия' END
) AS hierarchy_path
FROM
catalog l3
-- Уровень 3
LEFT JOIN translation l3_trans
ON l3.uuid = l3_trans.uuid
AND l3_trans.locale = 'ru-RU'
AND l3_trans.entity = 'Catalog'
AND l3_trans.key = 'name'
-- Уровень 2 (родитель уровня 3)
LEFT JOIN catalog l2
ON l3.parent_uuid = l2.uuid
LEFT JOIN translation l2_trans
ON l2.uuid = l2_trans.uuid
AND l2_trans.locale = 'ru-RU'
AND l2_trans.entity = 'Catalog'
AND l2_trans.key = 'name'
-- Уровень 1 (родитель уровня 2)
LEFT JOIN catalog l1
ON l2.parent_uuid = l1.uuid
LEFT JOIN translation l1_trans
ON l1.uuid = l1_trans.uuid
AND l1_trans.locale = 'ru-RU'
AND l1_trans.entity = 'Catalog'
AND l1_trans.key = 'name'
WHERE
l3.level = 3
ORDER BY
hierarchy_path;
Do not use Custom View for complex SQL, use database view instead. As the docs says:
DEPRECATED For backward compatibility only. You should NOT create new Custom Views.
Custom View expects a simple SELECT statement only. If you create a Custom View with a SELECT statement with some complex clauses… it may fail. You should change your Custom Views to database views. PHPMaker allows converting your Custom Views to database views provided that the database user have CREATE VIEW privilege.
I have a SQL query that works correctly and creates the expected result when I execute it directly in the database to create a regular view:
CREATE VIEW translation_catalog_name AS
SELECT DISTINCT
CONCAT(
COALESCE(l1_trans.value, 'Без названия'),
' -> ',
COALESCE(l2_trans.value, 'Без названия'),
' -> ',
COALESCE(l3_trans.value, 'Без названия')
) AS hierarchy_path
FROM
catalog l3
-- Уровень 3
LEFT JOIN translation l3_trans
ON l3.uuid = l3_trans.uuid
AND l3_trans.entity = 'Catalog'
AND l3_trans.key = 'name'
AND l3_trans.locale = 'ru-RU'
-- Уровень 2
LEFT JOIN catalog l2
ON l3.parent_uuid = l2.uuid
LEFT JOIN translation l2_trans
ON l2.uuid = l2_trans.uuid
AND l2_trans.entity = 'Catalog'
AND l2_trans.key = 'name'
AND l2_trans.locale = 'ru-RU'
-- Уровень 1
LEFT JOIN catalog l1
ON l2.parent_uuid = l1.uuid
LEFT JOIN translation l1_trans
ON l1.uuid = l1_trans.uuid
AND l1_trans.entity = 'Catalog'
AND l1_trans.key = 'name'
AND l1_trans.locale = 'ru-RU'
WHERE
l3.level = 3;
However, when I try to create this view directly using PHPMaker, it does not work and gives an error. Therefore, I created the view directly in my database.
But now, PHPMaker does not recognize this view as a table, even after I click the “Sync with Database” button. The view does not appear in the list of tables/views.
Could you please advise:
Why does PHPMaker fail to create this view directly?
Why is PHPMaker not recognizing the view created in the database after synchronization?
Is there any setting required to make database-created views visible in PHPMaker?
If the SQL is not generated by the built-in query builder, there are chances that query builder cannot parse the SQL and display it visually in the Builder tab. However, this does not necessarily mean that the SQL is invalid, you can verify its validity by executing it (see below) and check if it returns the data you want in the Result tab. And you can create the Custom View directly by click the OK button without switching to the Builder tab.
In that case, tell me how else can I solve the problem I need. I need to extract the hierarchy from one table: category → subcategory → name in one row. To use them in select.
If you enable Load tables dynamically under Database setting of your PHPMaker project, then make sure you did not enable the Show Loaded Tables Only option under the View menu of your PHPMaker project.