We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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