How to perform a subquery with a QueryBuilder object?

How can I perform a subquery with a QueryBuilder object?

Another question is how can I output the query after being processed? I'm only being able to output the query Phql but I can't see the binded parameters on the query.

Thank you.



81.8k

Queries are only resolved until they're executed, you can add an events manager to your connection to check which SQL's are sent to the database server:

<?php

$di['db'] = function() use ($config) {

    $eventsManager = new Phalcon\Events\Manager();

    //Listen all the database events
    $eventsManager->attach('db', function($event, $connection) {
        if ($event->getType() == 'beforeQuery') {
             $sqlVariables = $connection->getSqlVariables();
             if (is_array($sqlVariables)) {
                echo $connection->getSqlStatement(), ' ', join(', ', $sqlVariables), '<br>';
             } else {
                echo $connection->getSqlStatement(), '<br>';
             }
        }
    });

    $connection = new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => $config->database->host,
        "username" => $config->database->username,
        "password" => $config->database->password,
        "dbname" => $config->database->name
    ));

    $connection->setEventsManager($eventsManager);

    return $connection;
};

Nice! Thank you!

What about performing a subquery using the QueryBuilder? Is it possible or should I use raw sql?



81.8k

Subqueries aren't still supported in PHQL, we need to update the PHQL's planner to translate the subqueries correctly. You can use a raw sql or write the subquery as a join,

edited Apr '14

Hello,

Do you know how can I do this query with join? PHQL doesn't support subqueries wich is a big pain so I have to find alternatnives. Raw sql I didn't understood how to use.

SELECT * FROM location WHERE id NOT IN (SELECT locationid FROM userlocation WHERE user_id=1000);

I have two tables: one with locations and one with likes and dislikes from user to those locations. I want to retrive all the locations that the user didn't voted yet. I don't want to retrive locations that the user allready voted.

Thank you so much!