We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Convert phql query to query builder

Hello,

Can you help me to build this query using Query Builder ? I don't get it :/

    $phql = 'SELECT t1.id
    FROM Apps\Common\Models\Table1 t1 inner join Apps\Common\Models\Table2 t2 ON (t1.id_t2 = t2.id)
    inner join Apps\Common\Models\Table3 t3 ON (t3.id_t2 = t2.id)
    WHERE t1.col1 = :col1: AND t3.col2 = :col2:';

    $query = $this->modelsManager->createQuery($phql);
    $object = $query->getSingleResult(['col1' => $data->col1, 'col2' => $data->col2]);

Thanks



32.2k
Accepted
answer
edited Nov '18 
$builder = $this->modelsManager()->createBuilder();
$builder->from(['t1' => Table1::class])
->columns(['t1.id'])
->innerJoin(Table2::class, 't1.id_t2 = t2.id', 't2')
->innerJoin(Table3::class, 't3.id_t2 = t2.id', 't3')
->where(' t1.col1 = {col1:str}') // if col1 is not a string, change str by int/bool/etc
->andWhere('t3.col2 = {col2:str}');

$result = $builder->getQuery()->execute([
    'col1' => $data->col1,
    'col2' => $data->col2,
]);

More info. Good luck



41.3k
in reply to Emilio Degiovanni

Thank you. So it's not necessary to set the path ? Apps\Common\Models\

$builder = $this->modelsManager()->createBuilder();
$builder->from(['t1' => Table1::class])
->columns(['t1.id'])
->innerJoin(Table2::class, 't1.id_t2 = t2.id', 't2')
->innerJoin(Table3::class, 't3.id_t2 = t2.id', 't3')
->where(' t1.col1 = {col1:str}') // if col1 is not a string, change str by int/bool/etc
->andWhere('t3.col2 = {col2:str}');

$result = $builder->getQuery()->execute([
  'col1' => $data->col1,
  'col2' => $data->col2,
]);

More info. Good luck



32.2k

Sure you can use FQN Apps\Common\Models\TableModel but you can use the simplest way TableModel::class ::class reference

Good luck