Need help to create Reports

Need Help
1

I have the above query:
$orderRecords = $this->OrderRecords
->find(‘all’)
->where([‘Orders.customer_id’=>$data[‘customer_id’]])
->contain([
‘Orders’=>[‘Customers’, ‘Manufacturers’],
‘Products’=>[‘ProductModels’,‘ProductPhotos’],
]);
And I need to sum OrderRecords.quantity and make a group by OrderRecords.product_id and retrieve 1 foto for each product.
I have tried several approach with and without collection but with no luck.
If someone can help please!

2

I’ve answered this type question so often in laravel I really don’t feel up to writing all cake code here, so convert the answer to cake orn or query builder.

Here are 2 answers: First you could just use cake pdo instance:

A join and group by:

Looks like this when looped over:

RlzeSEp.jpg1366×564 64.6 KB

Code:

public function monthlyReport()
    {
        $bdate = Request::input('begindate');
        $edate = Request::input('enddate');

        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = `accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = `transactions`.`AccountID`))) where (`transactions`.`TransactionDate` Between '$bdate' and  '$edate') group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName`";
        
        $sth = DB::getPdo()->prepare($sql);
        $sth->execute();
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);
        $title = 'Monthly Report';
        return view('account.report', compact('quy', 'title'));
    }

Some logic is required during the loop. And this was a rough draft, not final version.

and orm something like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

I use cakephp, laravel, and yii2, just change example to cake style, and realize many complex queries require some trial and error to work out.

If you don’t know how to upload an image, learn that first, and if you get stuck ask a new question.

Note for reports I usually use the pdo instance, but use proper bindings.

I gave pdo instance example here:

There is also GitHub - PHPJasper/phpjasper: A PHP report generator

And here another link

https://laracasts.com/discuss/channels/laravel/complex-query-for-a-report

But with a little tweaking cake laravel or yii2 are all three similar.

3

After really looking over query builder (QB), for simple stuff such as:

$pets = TableRegistry::get('Pets');
        $query = $pets
                ->find()
                ->select(['petid', 'petname'])
                ->where(['id >' => 4])
                ->order(['petname' => 'ASC']);

Just example. I would use QB.

However:

For more complex stuff such as joins with group by I’d use the PDO instance, and write it equivalent to a “query scope”.

Just example:

    public function getTest()
    {
        $dbh = ConnectionManager::get('default');
        $sql = "SELECT dc_powners.ownerid, dc_powners.oname, ";
        $sql .= "COUNT(dc_pets.petid) AS CountOfpetid ";
        $sql .= "FROM dc_powners LEFT JOIN dc_pets ON ";
        $sql .= "dc_powners.ownerid = dc_pets.ownerid ";
        $sql .= "WHERE dc_powners.ownerid < :ownerid ";
        $sql .= "GROUP BY dc_powners.ownerid ";
        $sql .= "ORDER BY dc_powners.oname";
        $sth = $dbh->prepare($sql);
        $params = ['ownerid' => 4];
        $sth->execute($params);
        return $sth->fetchAll(\PDO::FETCH_OBJ);
    }

Of course in real app parameters are passed over.

Called from the controller via:

$quy = Pet::getTest();

Outputs:

Array
(
    [0] => stdClass Object
        (
            [ownerid] => 2
            [oname] => DIANE
            [CountOfpetid] => 10
        )

    [1] => stdClass Object
        (
            [ownerid] => 3
            [oname] => JEFFERY
            [CountOfpetid] => 2
        )

    [2] => stdClass Object
        (
            [ownerid] => 1
            [oname] => JIMMIY
            [CountOfpetid] => 11
        )

)
4

My preference is to use the QB. I discover on my several try and error that using group without selecting field… that doesn’t work ( I’m noob :slight_smile: ). I dont know why but after read your response, I Have achieved what I want with the query above:
$orderRecords = $this->OrderRecords->find(‘all’);

$orderRecords->select([‘Products.id’,‘ProductModels.description’,‘totalquantity’=>$orderRecords->func()->sum(‘quantity’)])
->where([‘Orders.customer_id’=>$data[‘customer_id’]])
->contain([
‘Orders’=>[‘Customers’, ‘Manufacturers’],
‘Products’=>[‘ProductModels’,‘ProductPhotos’],
‘Products.ProductPhotos’=> function ($q) {
return $q->where([‘ProductPhotos.type’ => ‘cover’]);
}
])
->group([‘product_id’]);
If I use product_id on select that doesn’t work either.

Thank you for your help jimgwhit