Support the ongoing development of Laravel.io →
Eloquent Views Blade
Last updated 7 months ago.
0

Have you googled grouping query with totals. If you can figure the regular sql for what you want, post it, then someone might be able to convert to eloquent. Or just use the regular sql. I myself use ms access to get a query, then I convert it to mysql. Of course you may not have ms access. But there or other tools available online to design a query. The raw mysql query is what you need first, then you can convert it to eloquent.

Last updated 7 years ago.
0

Not done in laravel, but here is a solution that can work, I think you will get the idea:
Controller code:

public function mytest() {
            $page='1';
        if (isset($_REQUEST['page'])) {
        $page = $_REQUEST['page'];
        }
        else {
        $page = '1';
        }
        $data['page'] = $page;
          $this->view->render('owner/mytest', $data);  
        }

View code, Yes accessing db in view, but almost have to in this case:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
<html xmlns="http://www.w3.org/1999/xhtml";>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
    
<?php
$db = \helpers\database::get();
$rowsperpage = '5';
//$offset = 0;
$page = $data['page'];
echo 'page=='.$page;
$offset = ($page - 1) * $rowsperpage;
echo 'offset=='.$offset. "<br>";
$pagingQuery = "LIMIT {$offset}, {$rowsperpage}";
$stmt = $db->select("SELECT * FROM ".PREFIX."powners ORDER BY oname " . $pagingQuery);   

foreach($stmt as $row){
                        $vowner = $row->ownerid;
			echo $row->oname . "<br>";
                        $stmt2 = $db->select("SELECT petname FROM ".PREFIX."pets WHERE ownerid = :vowner ORDER BY petname",
			 array('vowner' => $vowner));
                        foreach($stmt2 as $row2){
                            echo "-----". $row2->petname . "<br>";
                        }
		}

$pageno = $page + 1;
echo "<a href='".DIR."owner/mytest?page=".$pageno."'>next</a>";
?>

</body>
</html>

Here is sample output:
BLACKBURN
-----B8
-----JAVA
BOBBY
-----FLUFFYs
-----GIZMO's
-----suziedoggie
boy oh's
DANIEL
-----b10s
-----ztest3
DIANE
-----APRIL'S
-----BRAT
-----chocolate
-----GIANT
-----HOGAN
-----testoct25s
next

I'm listing owners with their pets, this is a test database I do all sorts of stuff on for testing.
Ignore names, just test data.

Last updated 7 years ago.
0

jimgwhit thank you for making me think...justed needed an extra brain for a moment. Group by worked for this. Here is my solution.

// get date form submit
$date = Input::get('date');
// get item categories
$cats = DB::table('saleitems')
                 ->select('category_id', DB::raw('count(*) as total'))
                 ->groupBy('category_id')
                 ->where('created_at', 'LIKE', '%'.$date.'%')
                 ->get();
// get items
        $items = DB::table('saleitems')
                 ->select('product_id', DB::raw('count(*) as total'), 'category_id', 'prod_name')
                 ->groupBy('product_id')
                 ->where('created_at', 'LIKE', '%'.$date.'%')
                 ->get();

then i used foreach loops and if's

<table width="100%" class="table table-bordered table-striped">
<tbody>
  @foreach($cats as $cat)
  <tr><th width="90%">{{Category::where('id', '=', $cat->category_id)->first()->cat_name}}</th><th>QTY</th></tr>
   @foreach($items as $item)
   @if($item->category_id == $cat->category_id)
   <tr><td width="90%">{{$item->product_id}} - {{$item->prod_name}}</td><td> {{$item->total}}</td></tr>
   @endif
   @endforeach
   <tr><th width="90%">&nbsp;</th><th>Total - {{$cat->total}}</th></tr>
  @endforeach
</tbody> 
</table>

worked like a charm!

0

Yes, similar, you grouped, I just selected as I went, but glad you got it.

0

Sign in to participate in this thread!

LoadForge

Your banner here too?

smithdp1 smithdp1 Joined 4 Apr 2015

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2022 Laravel.io - All rights reserved.