Why "dataStore" shows all rows from db?

Daniel commented on Aug 21, 2022

I'm pretty new with KoolReport. I'm using it in my app to generate some cool charts. The problem I'm having is that the "dataStore" shows all the rows from the DB in the view even when the query returns only one. I'm using the Eloquent data source to get the data.

MyReport.php file:

use App\Models\Stage;
use App\Models\Resources;

class MyReport extends \koolreport\KoolReport
{
    use \koolreport\laravel\Friendship;

    use \koolreport\clients\Bootstrap;

    function settings()
    {
        return array(
            "dataSources"=>array(
                "mysql"=>array(
                    "class"=>'\koolreport\laravel\Eloquent', // This is important
                )
            )
        );
    }

    function setup(){
        // finds only one row
        $this->src("mysql")->query(
            Stage::find($this->params["id"])
        )
        ->pipe($this->dataStore("stageDef")); 
        
        $this->src("mysql")->query(
            Resources::select('resourceName as Object name', 'amount as Amount in warehouse')
                ->join('warehouses', 'warehouses.warehouseId', '=', 'resources.resources_warehouseId')
                ->join('stages', 'stages.id', '=', 'warehouses.warehouseLocation')
                ->where('stages.id', '=', $this->params["id"])
        )
        ->pipe($this->dataStore("resources"));
    }
}

MyReport.view.php file:

<?php
use \koolreport\widgets\koolphp\Table;
?>
<html>
    <head>
    <title>Inventory</title>
    </head>
    <body>
        <!-- shows the "name"  from the result of the query on MyReport.php file -->
        <?php
            $data = $this->dataStore("stageDef");
            foreach($data as $d){
                echo (string) $d['name']    
            }            
        ?>
        
        <?php
        \koolreport\widgets\google\BarChart::create(array(
            "dataSource"=>$this->dataStore("resources")
        ))
        ?>


        <?php
        Table::create([
            "dataSource"=>$this->dataStore("resources")
        ]);
        ?>
    </body>
</html>

So, the thing is that the "dataStore("stageDef")" should only display one row, as you can see the query only fetches one row from the from DB, but in the view all the rows are displayed, I don't really know what is wrong, the "foreach" loop is the only way I have found to display the data I need, that is the reason of that loop on the view. The other "dataStore" works fine. Other thing I have tried is change the query for:

Stage::where('stages.id', '=', $this->params["id"])->first()

but is the same result.

Any help is appreciated.

Sebastian Morales commented on Aug 22, 2022

Where did you setup the input param "id"? Pls print it out at the beginning of report setup to check its value:

    function setup(){
        echo "param id = "; var_dump($this->params["id"]); echo "<br>";
        ...

In case you had an input with name = "id" in your report view, you would need to add the following traits and methods for the report to capture "id" in its params property:

class Report extends \koolreport\KoolReport
{
    use \koolreport\inputs\Bindable; // add this trait
    use \koolreport\inputs\POSTBinding; // add this trait if your form method is POST or inputs\GETBinding otherwise

    protected function defaultParamValues()
    {
        return array(
            "id"=>null,
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "id",
        );
    } 
    
    function setup()
    {
        var_dump($this->params["id"]); 
        ...

Another option is to use $_POST["id"] or $_GET["id"] directly or $this->input["id"] if you use a framework like Laravel, Symphony, etc.

KoolReport commented on Aug 22, 2022

You do this:

        $this->src("mysql")->query(
            Stage::where("id",$this->params["id"])->limit(1)
        )
        ->pipe($this->dataStore("stageDef"));

Let me know if it works.

Daniel commented on Aug 22, 2022

Hi, yes, it worked with:

$this->src("mysql")->query(
            Stage::where("id",$this->params["id"])->limit(1)
        )
        ->pipe($this->dataStore("stageDef"));

To answer Sebastian Morales, the "id" parameter is sent from the controller:

public function inventoryQuantityReport($id){
        $report = new MyReport(array("id"=>$id));
        $report->run();
        return view("reports.report", ["report"=>$report]);
    }

The report I'm trying to make just shows some data from the database, so it's basically a "GET" request.

Thanks for all the answers.

KoolReport's Forum

Why "dataStore" shows all rows from db? #2804

Build Your Excellent Data Report