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

Bug in PHQL Subquery?

I created a query with an EXISTS subquery, which works fine:

$query = (new Builder())
    ->from(['a' => Activity::class])
    ->columns(['id', 'name', 'DATE_FORMAT(booked_start, "%Y") AS year'])
    ->where('NOT EXISTS(SELECT e.id FROM ' . Event::class . ' e WHERE e.activity_id = a.id)');

But now when I try to add an order:

$query = (new Builder())
    ->from(['a' => Activity::class])
    ->columns(['id', 'name', 'DATE_FORMAT(booked_start, "%Y") AS year'])
    ->where('NOT EXISTS(SELECT e.id FROM ' . Event::class . ' e WHERE e.activity_id = a.id)')
    ->orderBy('year ASC');

I get this error: Column 'year' doesn't belong to any of the selected models (1), when preparing: ...

If I remove the subquery:

$query = (new Builder())
    ->from(['a' => Activity::class])
    ->columns(['id', 'name', 'DATE_FORMAT(booked_start, "%Y") AS year'])
    ->orderBy('year ASC');

It does work

Is this a bug?



79.0k
edited Sep '18

Perhaps try with:

  ->orderBy('booked_start');

just to rule out columns precedence.

I guess your Event table does not have column year?

Also why don't you do JOIN on those tables? DB will then filter out NOT exists case for you...



2.5k
edited Sep '18

Ordering by an existing column does work. And Event doesn't have a column year.

I know I can also use JOIN, I already did that in my code, I'm just trying to figure out if this is a bug.



79.0k
in reply to Kaz

And Event doesn't have a column year.

So that is the culprit. PHQL will try to use latter table.... for some reason. IDK whenever it's a bug or "by design" thing. Hope someone with more in-depth knowledge about it will reply.