How to use subqueries in Phalcon


Mar '15

May '15

6

2600

0


Thien
8.6k

Hi all

Today, I will share tip how to use subquery in Phalcon, When to working the project I have a problem use PHQL with sql below

$sql = 'SELECT u.id AS idUser, u.username AS username, c.content AS content, c.id AS id, 
(SELECT SUM(v.ositive) FROM vote v WHERE c.id = v.idObject AND v.object = ? ) AS positive,
(SELECT SUM(v.negative) FROM vote v WHERE c.id = v.idObject AND v.object = ? ) AS negative
FROM comment AS c
INNER JOIN user AS u ON u.id = c.idUser AND c.object = ?
WHERE c.idObject = ?';

So I'm search on google then I get result Phalcon does not support subqueries right now . To solve this problem, you can use api Resultset

<?php

namespace Zphalcon\Models;

use Phalcon\DI\FactoryDefault;
use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

/**
 * Class ModelBase
 *
 * @package Zphalcon\Models
 */
class ModelBase extends Model
{


    public static function getBuilder()
    {
        $di = FactoryDefault::getDefault();

        return $di->get('modelsManager')->createBuilder();
    }

    public static function getConnection()
    {
        $di = FactoryDefault::getDefault();

        return $di->get('db');
    }

    /**
     * @param $idObject
     * @param $object
     *
     * @return mixed
     */
    public function getComments($objectString)
    {
        $sql = 'SELECT u.id AS idUser, u.username AS username, c.content AS content, c.id AS id,
        (SELECT SUM(v.positive) FROM vote v WHERE c.id = v.idObject AND v.object = ? ) AS positive,
        (SELECT SUM(v.negative) FROM vote v WHERE c.id = v.idObject AND v.object = ? ) AS negative
        FROM comment AS c
        INNER JOIN user AS u ON u.id = c.idUser AND c.object = ?
        WHERE c.idObject = ?';

        $object = new Comment();
        $params = [Vote::OBJECT_COMMENT, Vote::OBJECT_COMMENT, $objectString, $this->getId()];
        return (new Resultset(null, $object, $object->getReadConnection()->query($sql, $params)))->toArray();
    }
}

Also you can use Phalcon PDO for this case , you can take look here. And good new in version 2 Phalcon have supports the subquery in PHQL

You like tip, if so share it


Thien
13.1k
edited Mar '15
Mar '15

If you want to Phalcon PDO, just to it

$pdo = \Phalcon\DI::getDefault()->getDb();
$sql = $pdo->prepare($query);
$sql->setFetchMode(\Phalcon\Db::FETCH_ASSOC);
$sql->execute();
$results = $sql->fetchAll();

Thien, I can recognize the code :)


Thien
13.1k
edited Apr '15
Apr '15

Yep:)

Realy I'm learning many skill Phalcon from your, and now I'm working for company majors in Phalcon

Thanks

I'm glad to hear that! I accidentally voted negative. Sorry :(

Native support for subqueries is available in Phalcon 2.0.2

Great news ! Is there any documentation about or where it could be found in .zep file in order to play with it ? :) Thanks !