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. MVC

How I can make this query (inner join) work? Thanks.

$builder = $this->modelsManager->createBuilder();
$builder->from('Orders');
$builder->addFrom('Publishers', 'p');
$builder->innerJoin('Orders', 'o.publisher_id = p.id', 'o');
$builder->where('p.manager_id = :employee_id:', ['employee_id' => $employee_id]);
$builder->orderBy('o.id DESC');
$orders = $builder->getQuery()->getSingleResult();

SQL: SELECT o.* FROM Orders o INNE JOIN Publishers p ON o.publisher_id = p.id WHERE p.manager_id = $employee_id ORDER BY o.id DESC;

model: Orders / table: xms_orders

model: Publishers / table: xms_publishers



6.4k
Accepted
answer

Try

<?php

$builder
    ->addFrom('Orders', 'o')
    ->innerJoin('Publishers', 'o.publisher_id = p.id', 'p')
    ->where('p.manager_id = :employee_id:', ['employee_id' => $employee_id])
    ->orderBy('o.id DESC')
;