Add "count(*)" result in list table.

<XMP> Hello All. I am new in programming and phalcon. What I am trying to do is execute below sql statement.

select ,count() from table_name GROUP BY date, status;

and make table list including "Count(*)" result.

I could get the result (with no count(*)) and make list by below.

$Invoices = Invoices::find(["group" => "status, date","order" => "date"]);

but I coundn't find out how to get count() number and add count() result column.

If anyone can help. I appliciate it.

edited May '19

I am trying like this, but no luck.

Controller

    $Invoices = Invoices::find(["group" => "status, date","order" => "id"]);

    $this->view->setVar($count= Invoices::count(
        [
            "group" => "status,date",
            "order" => "id"]
        ]
    ));

View

<tr>

    <td>{{ invoice.id }}</td>

    <td>{{ invoice.inv_number }}</td>

    <td>{{ invoice.cust }}</td>

    <td>{{ invoice.date }}</td>

    <td>{{ invoice.status }}</td>

    <tb>{{ '(count)' }}</tb>

</tr>


751

Try this:

$Invoices = Invoices::find([
    "columns" => "id, inv_number, cust, date, status, count(*) as count",
    "group" => "status, date",
    "order" => "date"
]);

Thank you so much!

edited May '19

oooh. I cant get the record from relation model. I need to get rid of "Columns". But I need them to add results of group_Concat and Count(). Is there any solution?



751

Try this:

        $hydration = new Invoices();
        $table = $hydration->getSource();
        $sql = "select id, inv_number, cust, date, status, count(*) as count from $table group by status, date order by date";
        $invoices = new \Phalcon\Mvc\Model\Resultset\Simple(null, $hydration, $hydration->getReadConnection()->query($sql));
edited May '19

You can always use query builder and make joins and get both full models and group count.