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).

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?

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 :)

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?