subquerys

they work use phql

https://docs.phalcon.io/en/3.0.0/reference/phql.html

https://github.com/phalcon/cphalcon/issues/10453

They work but not all possible syntaxes. Like you can't do SELECT * FROM (SELECT)

As Wojciech says you cannot do SELECT * FROM (SELECT). If you want to use this please look at "Views" in your database engine. It allow you to pull data from query saved as View (also via phql as well as via ORM), but remember - it serve only for PULL data not for Insert, update or delete

Nested selects in MySQL are big under performers and should be avoided at all costs.

Simply put, MySQL/MariaDB cannot handle such subqueries it as PostgreSQL for instance can.

well heres an my example

$invoices = ProjectInvoices::query()
    ->columns([
      "Manager\Models\ProjectInvoices._",
      "Manager\Models\ProjectInvoices.unique",
      "Manager\Models\ProjectInvoices.title",
      "(SELECT SUM(Manager\Models\InvoiceItems.amount) FROM Manager\Models\InvoiceItems WHERE Manager\Models\InvoiceItems.invoice = Manager\Models\ProjectInvoices._) as items",
      "(SELECT SUM(Manager\Models\InvoiceItems.price * Manager\Models\InvoiceItems.amount) FROM Manager\Models\InvoiceItems WHERE Manager\Models\InvoiceItems.invoice = Manager\Models\ProjectInvoices._) as value",
      "(SELECT SUM(Manager\Models\InvoicePayments.amount) FROM Manager\Models\InvoicePayments where Manager\Models\InvoicePayments.invoice = Manager\Models\ProjectInvoices._) as paid",
    ])
    ->leftJoin("Manager\Models\InvoicePayments","Manager\Models\InvoicePayments.invoice = Manager\Models\ProjectInvoices._")
    ->where("Manager\Models\ProjectInvoices.project = :project:")
    ->bind([
      "project" => $this->project->_
    ])
    ->execute()

then this should work fine, what's a problem? Oh wait this is example from your app, yea this works fine, have simila things.

Use model namespace aliases dude :D Also with query builder instead of ProjectInvoices you can have pj so you can have something like:

->from(['pj' => 'M:ProjectInvoices'])

And then use only pj

@Jurigag , idk how thats done

then this should work fine, what's a problem? Oh wait this is example from your app, yea this works fine, have simila things.

Use model namespace aliases dude :D Also with query builder instead of ProjectInvoices you can have pj so you can have something like:

->from(['pj' => 'M:ProjectInvoices'])

And then use only pj

HIi! Thank you for you responses. I should analyze the use of views for resolve queries as "Select * , SELECT()...FROM" and the alternative of Hudson Nicoletti. I am blocked with Phalcon, the functions and procedures saved on database require a associated model? How i use DUAL or similar?

regards!

What you mean associated model? If you have your own functions and procedures in database then you need to add them as dialect extension to make PHQL understand them. There is example for group_concat(with seperator) from my code:

<?php
/**
 * Created by PhpStorm.
 * User: User
 * Date: 16.09.16
 * Time: 14:39
 */
namespace Suzuki\App\Dialect\Extensions;

use Phalcon\Db\Dialect;
use Suzuki\App\Interfaces\DialectFunctionInterface;

/**
 * Class GroupConcat
 * @package Suzuki\App\Dialect\Extensions
 */
class GroupConcat implements DialectFunctionInterface
{
    /**
     * @return \Closure
     */
    public function getFunction()
    {
        return function (Dialect $dialect, $expression) {
            $arguments = $expression['arguments'];
            if (!empty($arguments[1])) {
                return sprintf(
                    " GROUP_CONCAT(%s SEPARATOR %s)",
                    $dialect->getSqlExpression($arguments[0]),
                    $dialect->getSqlExpression($arguments[1])
                );
            }

            return sprintf(
                " GROUP_CONCAT(%s)",
                $dialect->getSqlExpression($arguments[0])
            );
        };
    }
}

<?php
/**
 * Created by PhpStorm.
 * User: User
 * Date: 16.09.16
 * Time: 13:59
 */
namespace Suzuki\App\Dialect;

use Suzuki\App\Interfaces\DialectFunctionInterface;

/**
 * Class Mysql
 *
 * @package Suzuki\App\Dialect
 */
class Mysql extends \Phalcon\Db\Dialect\Mysql
{
    /**
     * Mysql constructor.
     */
    public function __construct()
    {
        $this->registerCustomFunctions();
    }

    /**
     * Register Custom dialect functions
     */
    public function registerCustomFunctions()
    {
        $customFunctions = [
            'GROUP_CONCAT'  => 'GroupConcat',
            'DATE_INTERVAL' => 'DateInterval',
        ];
        foreach ($customFunctions as $key => $value) {
            $className = 'Suzuki\\App\\Dialect\\Extensions\\'.$value;
            /** @var DialectFunctionInterface $object */
            $object = new $className;
            $this->registerCustomFunction($key, $object->getFunction());
        }
    }
}