Sneaking in subqueries into PHQL

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.

@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!

@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!

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

@Daniel, you can look here https://phalcontip.com/discussion/36/how-to-use-subqueries-in-phalcon

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!