Hi, how are you?
Currently, ¿subquerys don't worok on phalcon? I haven't found examples.
thank you! regards!
|
Jan '17 |
8 |
625 |
0 |
they work use phql
https://docs.phalcon.io/en/3.0.0/reference/phql.html
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
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());
}
}
}