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

How to build a Paginated list with Join, Group and Sum (Aggregate-Columns)

Hi

the table-structure:

Supplier -> 1:n -> Invoices -> 1:n -> InvoiceItems

Now i want to display a paginated list with the columns: invoice.id, supplier.name, SUM(invoiceitem.price)

Realy easy every-day-task, but (again) i don't know how to realize this with Phalcon. I can find some examples for each part of this requirement (Join, Pagination, Sum), but i can't find anything about how to combine these 3 parts.

Pagination and Join to Supplier works well:

// controller
<?php
$queryBuilder = $this->modelsManager->createBuilder()
    ->from('Invoices')
    ->join('Suppliers')
;

$paginator = new Phalcon\Paginator\Adapter\QueryBuilder([
    "builder" => $queryBuilder,
    "limit"   => 20,
    "page"    => 1
]);
?>
// view
<?php foreach ($paginator->items as $invoice): $supplier = $invoice->Suppliers; ?>
    <?= $invoice->id ?>
    <?= $supplier->name ?>
<?php endforeach ?>

But adding the aggregate Column doesn't work as expected and i can't find anything similar in the docs. I tried:

$queryBuilder = $this->modelsManager->createBuilder()
    ->from('Invoices')
    ->join('Suppliers')
    ->join('InvoiceItems')
    ->columns([
        'Invoices.*',
        'Suppliers.*',
        'SUM(InvoiceItems.price)',
    ])
    ->groupBy('Invoices.id')
;

$paginator = new Phalcon\Paginator\Adapter\QueryBuilder([
    "builder" => $queryBuilder,
    "limit"   => 20,
    "page"    => 1
]);

This results in Phalcon\Mvc\Model\Row where i can't access any imaginable property. Neither $row->id nor $row->Suppliers are existent.

So what's wrong or what am i missing? Is there an other Solution for such a list?



3.5k

damn .... got it.

The Solution works. The mistake was in the view/access. The Acces to the requested Models works with LOWER CamelCase properties, so it's:

// view
<?php foreach ($paginator->items as $row): ?>
    <?= $row->suppliers->name?>
    <?= $row->invoices->id ?>
    <?= $row->price ?>
<?php endforeach ?>

Some hints on the behavior of Joined Models and the resulting Phalcon\Mvc\Model\Row in the docs wohld be nice. (Or am i blind?)



3.5k

... found the confusing point.

Bug or Feature? Whats the right way?

$row = $this->modelsManager->createBuilder()
  ->from('Invoices')
  ->join('Suppliers')
  ->getQuery()->execute()->getFirst()
;

$row; // = instance of Model "Invoices"
$row->id; // = value from invoices.id
$row->suppliers; // = instance of Model "Suppliers"
$row->Suppliers; // = instance of Model "Suppliers"

//-----//
// BUT //
//-----//

$row = $this->modelsManager->createBuilder()
    ->from('ExpenseInvoices')
    ->join('Suppliers')
    ->columns(['Invoices.*','Suppliers.*',])
    ->getQuery()->execute()->getFirst()
;

$row; // = instance of "Phalcon\Mvc\Model\Row"
$row->id; // = undefined
$row->suppliers; // = instance of Model "Suppliers"
$row->Suppliers; // = undefined


6.4k
Accepted
answer

Read this

With your first example you will get as result an instance of Phalcon\Mvc\Model\Resultset\Simple that is a set of Invoices models, but with the second example the object is a Phalcon\Mvc\Model\Resultset\Complex that is a set of Phalcon\Mvc\Model\Row composed by two models (Invoices and Suppliers) each row.

Remove ->getQuery()->execute()->getFirst() for each builder and echo getPhql() to see the query that will be executed