We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

How can I convert this MySQL subquery to PHQL?

I have test this MySQL query and it worked well:

SELECT * FROM
    (SELECT `category_tb`.`category_id` AS `category_id`, `category_tb`.`category_name` AS `category_name`, `category_tb`.`category_level` AS `category_level`, `category_tb`.`category_parent` AS `category_parent` 
    FROM `category_tb`
    WHERE `category_tb`.`category_level` = 2) AS `category_level_2`
LEFT JOIN 
    (SELECT `category_tb`.`category_id` AS `category_parent_id`, `category_tb`.`category_name` AS `category_parent_name` FROM `category_tb`
    WHERE `category_tb`.`category_level` = 1) AS category_level_1
ON `category_level_2`.`category_parent` = `category_level_1`.`category_parent_id`

And I tried to write it as PHQL

$categoryPHQL = "
            SELECT * FROM
                (SELECT Categories.category_id AS category_id, 
                  Categories.category_name AS category_name, 
                  Categories.category_level AS category_level, 
                  Categories.category_parent AS category_parent 
                FROM Categories
                WHERE Categories.category_level = 2) AS category_level_2
            LEFT JOIN 
                (SELECT Categories.category_id AS category_parent_id, 
                        Categories.category_name AS category_parent_name FROM Categories
                WHERE Categories.category_level = 1) AS category_level_1
            ON category_level_2.category_parent = category_level_1.category_parent_id";

But Phalcon thrown error:

Syntax error, unexpected token (, near to 'SELECT Categories.category_id AS category_id...

So I think I cannot write

    SELECT * FROM
                (SELECT Categories.category_id AS category_id,...

But I have no idea to fix this. Please help me to resolve this case.



145.0k
Accepted
answer
edited Oct '16

Why you need this subquery ? You can write the same query without subquery. PHQL is just not supporting any of requests like SELECT * FORM (SELECT * FORM XYZ) t1. PHQL is sql but for selecting using models, how he should hydrate such a data into models when selecting from temporary table ?

Why cant you just do:

 "SELECT Categories.category_id AS category_id, 
                  Categories.category_name AS category_name, 
                  Categories.category_level AS category_level, 
                  Categories.category_parent AS category_parent
                FROM Categories AS category_level_2
            LEFT JOIN Categories AS category_level_1 ON category_level_1.category_level = 1 AND category_level_2.category_parent = category_level_1.category_parent_id
                WHERE Categories.category_level = 2
               "


6.0k

Thanks so much.

I think I must practice more with SQL :D

I have tested and it's OK now (but we must move WHERE condition to last line)

SELECT category_level_2.category_id, 
                  category_level_2.category_name, 
                  category_level_2.category_level, 
                  category_level_2.category_parent,
                  category_level_1.category_name AS category_parent_name
                FROM Categories AS category_level_2
            LEFT JOIN Categories AS category_level_1 
            ON category_level_1.category_level = 1 AND category_level_1.category_id = category_level_2.category_parent
            WHERE category_level_2.category_level = 2";
edited Oct '16

Performanse wise - subqueries in MySQL are very bad and should be avoided. Use JOINs as @Jurigag presented here.

If you really need subqueries, you'll have to switch to a PostgreSQL but then you also need to use raw PDO not ORM.

Well this query can be build as well with query builder, so no need to learn sql even :P

Thanks so much.

I think I must practice more with SQL :D

I have tested and it's OK now (but we must move WHERE condition to last line)

SELECT category_level_2.category_id, 
                 category_level_2.category_name, 
                 category_level_2.category_level, 
                 category_level_2.category_parent,
                 category_level_1.category_name AS category_parent_name
               FROM Categories AS category_level_2
           LEFT JOIN Categories AS category_level_1 
           ON category_level_1.category_level = 1 AND category_level_1.category_id = category_level_2.category_parent
           WHERE category_level_2.category_level = 2";


6.0k

I think if we don't know about SQL we cannot use query builder :P

Well this query can be build as well with query builder, so no need to learn sql even :P

Thanks so much.

I think I must practice more with SQL :D

I have tested and it's OK now (but we must move WHERE condition to last line)

SELECT category_level_2.category_id, 
                 category_level_2.category_name, 
                 category_level_2.category_level, 
                 category_level_2.category_parent,
                 category_level_1.category_name AS category_parent_name
               FROM Categories AS category_level_2
           LEFT JOIN Categories AS category_level_1 
           ON category_level_1.category_level = 1 AND category_level_1.category_id = category_level_2.category_parent
           WHERE category_level_2.category_level = 2";

@Jonathan: Thanks, I will notice about this.