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

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
in reply to Wojciech Ślawski

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";


79.0k
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.



145.0k
in reply to Envy

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
in reply to Wojciech Ślawski

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.