PHQL sub query support

[bahadirbirsoz]

Hello,

I'm in need of using sub query. I've looked for similar issues but haven't been able to find a proper answer. Does phql support sub queries?

Let me tell you the use case. I have some complex queries with left and inner joins and pagination (limit). But the pagination must work according to main entity (only limit from part). So, here's my solution in sql:

SELECT 
   bill.*,
   bill_line.*,
   bill_payment_part.*
FROM
   ( SELECT * from bill WHERE customer_id =:customer_id: LIMIT 0,25 ) as bill
LEFT JOIN bill_line ON bill_line.bill_id = bill.id
LEFT JOIN bill_payment_part ON bill_payment_part.bill_id = bill.id

Is there a way to execute this query in phql. I've the following, but got syntax error :

SELECT 
   bill.*,
   bill_line.*,
   bill_payment_part.*
FROM
   ( SELECT * from \Project\Models\BillModel as bill WHERE customer_id =:customer_id: LIMIT 0,25 ) as bill
LEFT JOIN \Project\Models\BillLineModel as bill_line ON bill_line.bill_id = bill.id
LEFT JOIN \Project\Models\BillPaymentPartModel as bill_payment_part ON bill_payment_part.bill_id = bill.id

So, if phql doesn't support sub query, the I can run this as raw sql query. In that case, can I cast the raw query result to complex resultset Phalcon\Mvc\Model\Resultset\Complex object. Well I'm sure I can somehow handle casting objects, but is there a phalcon way to achieve it?

[quasipickle]

What was the syntax error? I believe PHQL supports anything PDO does, so subqueries should work.

[bahadirbirsoz]

Thanks for the reply. Well, the query above was a representation of the query that is built by my app. error message is attached with the original query. I got the same error with much simpler queries as well.

error.log

By the way,

• The query works fine without wrapping the FROM part
• Raw query works fine when I replace the class names with table names
• Sub query works fine when placed in WHERE WHERE mainentity.id IN ( some_sub_query ) , but I couldn't get it to work as a join statement or from statement.
  PHP Version 7.4.6
  Phalcon 4.1.0

[bahadirbirsoz]

So, I think PHQL does not support view-like subquery usage. I've decided to move on. The only purpose of this was to handle pagination. I've decided to run 2 separate queries instead of subquery.

• First query is to get id array (with limit)
• In the second query I'm using entityalias.id IN ( returned_ids ) instead of LIMIT.

[quasipickle]

Yeah, I seem to recall PHQL or PDO having issues with views. I couldn't tell you what they were though.

If you're wanting to do pagination, look into the built-in Phalcon methods for handling pagination. It'll certainly be a lot more straightforward than that beast of a query.