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

Sneaking in subqueries into PHQL

I'm aware that subqueries are not supported by PHQL, but I would still like to find some way to put them into queries. I also know that I could simply use raw SQL, but then I would lose the convenience of query builder... Basically I'm looking for a way to embed some raw SQL into WHERE, for example, it could look like this:

$query->andWhereSQL('(SELECT COUNT(*) FROM ProductCategory WHERE categoryID=123 AND ProductCategory.productID=Product.ID) > 0');

So, instead of interpreting this as PHQL, it would be included in the generated query directly. This would be useful not only for subqueries, but specific database functions too.

I have tried looking at the getIntermediate() and setIntermediate() methods to see if I can somehow trick this in. Perhaps it even could use the full query builder functionality in a way like this:

$query->andWhere(':subquery: > 0', array('subquery' => array('type' => 'literal', 'condition' => '(SELECT COUNT(*) FROM ProductCategory WHERE categoryID=123 AND ProductCategory.productID=Product.ID)')));

Is there a way I can alter the intermediate data to implement this?



98.9k

You don't need a subquery here:

$products = $this->modelsManager->createBuilder()   
    ->from('Products')
    ->join('ProductsCategory')
    ->where('Products.CategoryId = 123');

Product and ProductCategory tables have many-to-many relationship. But I see in docs that it's supported though, so it might work, I'll try to play with it.

However, while this is a simple setup, I'll also have more complex cases where the only option would be a subquery. Is there any way I could implement this as per my question above? If altering the intermediate data would not work, is there a way to generate the raw SQL query from query builder?

Since I really needed to have the subqueries working as query conditions, here's how I implemented this.

PHQL apparently allows custom database functions after all (the more you read the docs, the more you find), which is good and gave me the idea to "implement" subqueries as functions. The syntax is like this:

$query->andWhere('SUBQUERY("SELECT COUNT(*) FROM ProductCategory") > 0)');

The custom DB adapter replaces this syntax to a regular subquery (subquery may not include single quote characters in the current implementation):

class MyPdoAdapter extends \Phalcon\Db\Adapter\Pdo\Mysql
{
    public function query($sqlStatement, $placeholders = NULL, $dataTypes = NULL)
    {
        if (strpos($sqlStatement, 'SUBQUERY('))
        {
            $sqlStatement = preg_replace("/SUBQUERY\('([^']+)'\)/", '(\1)', $sqlStatement);
        }

        return parent::query($sqlStatement, $placeholders, $dataTypes);
    }
}

Of course, I'll be happy to get rid of this once subqueries are supported, but this will work for now.



2.2k

@Rinalds: Thanks man you are a life saver! I was in the middle of taking Phalcon code apart when I found your hack and applied the solution to my problem. See for your self if you are curious: https://forum.phalcon.io/discussion/1446/how-can-i-extend-the-phql-syntax-is-parser-lemon-outdated- Thank you again!



508

@Rinalds: Thanks for this, really helped. I had a few issues at first so I modified it slightly. The strpos should be comapred with type compare and I altered the preg_replace as well.

public function query($sqlStatement, $placeholders = NULL, $dataTypes = NULL)
{
    if (strpos($sqlStatement, 'SUBQUERY(') !== false) {
        $sqlStatement = preg_replace("/SUBQUERY\('(.+?)'\)/", '(\1)', $sqlStatement);
    }

    return parent::query($sqlStatement, $placeholders, $dataTypes);
}

Then I used it like so

$query->andWhere('SUBQUERY("SELECT count(*) FROM dealer_areas Areas where Areas.dealer_id = Dealers.id and Areas.state_cd = :state ") > 0', array('state' => $state));

Thanks again!

edited May '15

Where to add this if i used the phalcon bootstrap? And how to use it in a query in a model like:

$this->getModelsManager()->executeQuery("SELECT model.name, (SELECT  COUNT(*) FROM robots WHERE robots.model = model.id) FROM model");

Please help

Native support for subqueries is available in Phalcon 2.0.2

Woau that's awesome. but I cannot see the windows DLL of that version. Will they be avaliable any time soon?

Native support for subqueries is available in Phalcon 2.0.2

It's not released yet, probably in the next weeks :)

Woau that's awesome. but I cannot see the windows DLL of that version. Will they be avaliable any time soon?

Native support for subqueries is available in Phalcon 2.0.2

Oh...mi gozo es un pozo. I cannot wait to I'll rather replace them with joins, old-school

It's not released yet, probably in the next weeks :)

Woau that's awesome. but I cannot see the windows DLL of that version. Will they be avaliable any time soon?

Native support for subqueries is available in Phalcon 2.0.2

Hi! How are you??? A question: There is some new thing about implementation of subquery on Phalcon?

Thanks you, Regards!