KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

LeftJoin does not work throws exception #1844

Open MarkoS opened this topic on on Jan 12 - 5 comments

MarkoS commented on Jan 12

Hi all,

Hi guys, I am trying to do LeftJoin of two data sets and I am getting error Message: Invalid argument supplied for foreach()

My code looks like this:

// 1st
$stanjeRobe = $this->src('automaker')
                        ->query($prodaja_radnje['priprema'])
                        ->params(array(
                            ":korisnik"=>$dodDobavljac
                        )) // ;
                        ->pipe($this->dataStore("stanjeRobe"));


// 2nd store
$prodajaRadnje = $this->src('automaker')
                    ->query($prodaja_radnje['sve'])
                    ->params(array(
                        ":korisnik"=>$dodDobavljac,
                        ":start"=>$this->params["dateRange"][0],
                        ":end"=>$this->params["dateRange"][1],
                    ))//;
                    ->pipe($this->dataStore("prometRobe"));


$new_store = $this->dataStore('prometRobe')->leftJoin($this->dataStore('stanjeRobe'),array(
                    "SifraMat"=>"SifraMat"
                ));

I have tried this way as well, same thig but a bit different:

// ## 1st
                $this->src('automaker')
                ->query($prodaja_radnje['priprema'])
                ->params(array(
                            ":korisnik"=>$dodDobavljac
                ))
                ->pipe($this->dataStore("stanjeRobe"));


// ## 2nd
                $this->src('automaker')
                ->query($prodaja_radnje['sve'])
                ->params(array(
                        ":korisnik"=>$dodDobavljac,
                        ":start"=>$this->params["dateRange"][0],
                        ":end"=>$this->params["dateRange"][1],
                ))
                ->pipe($this->dataStore("prodajaRadnje")); 


// this throws an error: Message:  Invalid argument supplied for foreach()
$new_store = $this->dataStore('prodajaRadnje')->leftJoin($this->dataStore('stanjeRobe'),array(
                    "SifraMat"=>"SifraMat"
                ));

I am using Join for now, as below and works fine but I need left join to have correct data.

$join = new Join($stanjeRobe,$prodajaRadnje,array(
"SifraMat"=>"SifraMat","BrojSklad"=>"BrojSklad"
)); 

Is there something different in documentation (https://www.koolreport.com/docs/datastore/overview/#join-methods-leftjoin) that I am missing or I did not use it properly here.

I don't get what argument I have to provide, there is no documentation for it (not one which works)

Note: MySQL query with JOIN takes ages to execute and this Join is my only bet (for now).

KoolReport commented on Jan 12

Hi MarkoS,

Please try to move this code

$new_store = $this->dataStore('prodajaRadnje')->leftJoin($this->dataStore('stanjeRobe'),array(
                    "SifraMat"=>"SifraMat"
                ));

to the view.

The reason it failed is that at the setup() time, there is no data for it to do leftJoin. So basically, when you move to the view, it will work because at that time, report has been run and data is available.

Let us know if it works.

MarkoS commented on Jan 12

Hi thanks for reply. Yeah! It does work, I have to check if data is correct. One problem I have is formatting for number.

// Message: number_format() expects parameter 1 to be float, string given

"TrenZalIznos"=>array(
                        "label"=>"Vrijednost zalihe (KM)",
                        "type"=>"number",
                        "decimals"=>2,
                         "formatValue"=>function($value,$row){
                            $pad = str_pad(number_format($value,0,"",""),2,"0",STR_PAD_LEFT);
                            return number_format($value,2,".","");
                        },
                    )

Looks like that it has been modified (changed to string or so) and throws error for number formatting. Value is float, I can limit that to 2 decimal places in query with ROUND(sum(Iznos), 2), but why "decimals"=>2 does not work?

KoolReport commented on Jan 12

When you use "formatValue", you are in full control mode, you provide your own formatting , other settings like "decimals" "thousandSeparator" won't affect.

MarkoS commented on Jan 13

Oh okey, I got it working, just by using ROUND inside mysql query. It's bit strange how and why it worked fine before LeftJoin. But anyway thanks for help, it did a job :)

MarkoS commented on Jan 13

There is one thing I cam across since I moved everything to view. I am getting same error when there is no data in dataStore. Sure, there's no data to be joined in one or both dataSets. I need to check if those dataSources cointain data and than execute Join. But still keeping view (data table) displayed as empty.

This kind of works but it's probably not the best way

            $hulahop = $this->dataStore('prodajaRadnje');
            
             if($hulahop[0]['Grupa'] != ""){ 
                 echo "Data has been found, proceed";
                 // run LeftJoin
                 $new_store = $this->dataStore('prodajaRadnje')->leftJoin($this->dataStore('stanjeRobe'),array(
                    "Grupa"=>"Grupa",
                    "NazivRadnje"=>"CM_Radnja",                    
                )); 
             } else {
                 echo "No data found";
                 // present any store to the variable so DataTables does not complain.
                 $new_store = $this->dataStore('prodajaRadnje');                   
             }

Which way would you suggest?

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed

None