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

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

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

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

Good luck