KoolReport's Forum

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

How to do LEFT JOIN? #970

Closed Eugene opened this topic on on Jul 4, 2019 - 14 comments

Eugene commented on Jul 4, 2019

Hi, Could you recommend me something for the subj:

I use the Join process to join sql and css data. for example I have 2 tables sql

id1    value
1	100
2	200
3	300

css

id2	value
1	10
2	20

What i wanna get is

id	valuesql	valuecss
1	100		10
2	200		20
3	300		n/a
KoolReport commented on Jul 5, 2019

We are sorry that we did not have the left join process. What we do have is the leftJoin() method in DataStore. May be you could left join two datastores at the end. I will mark this thread as [suggestion] for further enhancement.

Eugene commented on Jul 5, 2019

I think I can use it... thank you so much

but for my understanding - what is the difference - join 2 sources and put in one DataStore vs put each source in the separate DataStore and join them after that? What are the advantages and disadvantages?

Eugene commented on Jul 5, 2019

Do you have something like CalculatedColumn for DataStore?

The problem is that I need to do some calculations that used data from both tables and put the result in the new column.

If I do join on the process level (before i put data to any DataStore) - i can do it, but how to do the same with DataStore?

I think i can use process() for that and use CalculatedColumn but can i use function($data){...} for calculation?

KoolReport commented on Jul 5, 2019

You use process() method of DataStore.

Eugene commented on Jul 5, 2019

Something went wrong :-(

I tried to update my report file using left join etc but get the empty table

What i have 1. Two DataStores dataStore('stock') and dataStore('from_csv')

$this->src('quinos')
            ->query($query)
            ->params($query_params)
            ->saveTo($fromsql)
            ->pipe($this->dataStore('stock'));

and

$this->src('fact')
                ->saveTo($fromcsv)
                ->pipe($this->dataStore('from_csv'));
  1. this is the part of my report file
$tmp_store = $this->dataStore('stock')->leftJoin($this->dataStore('from_csv'),array(
                "name" => "name"
            ));
            $tmp_store->process (new CalculatedColumn(array(
                "diff" => function($data){
                    return $data["fact"]-$data["balance"];
                },
            )))
                ->pipe($this->dataStore("joined_stock"));

If i use the same CalculationColumn code after using $join = new Join($fromsql, $fromcsv, array("name" => "name")); everything is ok excepting that i need the left join

KoolReport commented on Jul 5, 2019

There is no pipe to dataStore joined stock anymore, it should be:

$joined_stock_datastore = $tmp_store->process (new CalculatedColumn(array(
                "diff" => function($data){
                    return $data["fact"]-$data["balance"];
                },
)));
Eugene commented on Jul 5, 2019

Ah so but what to do in the report view file in this case. I did

Table::create(array(
            "dataStore" => $this->dataStore('joined_stock'),

but it will not work more so how to link the $joined_stock_datastore with the Table?

KoolReport commented on Jul 5, 2019

then you do:

$joined_stock_datastore = $tmp_store->process (new CalculatedColumn(array(
                "diff" => function($data){
                    return $data["fact"]-$data["balance"];
                },
)));

Table::create(array(
    "dataStore"=>$joined_stock_datastore
));

Eugene commented on Jul 5, 2019

Thank you it works.

Could you also suggest a way to merge 2 datastore with the same set of fields. i can do it via each() and append() but may be you have another way

KoolReport commented on Jul 5, 2019

We have the join() in DataStore as well that you may use to join two datastore on keys.

Eugene commented on Jul 5, 2019

I meant merge rows... like union all

KoolReport commented on Jul 5, 2019

Oh sure, you can use each() and append() method, or even that loop through first datastore to append to another:

foreach($this->dataStore("abc") as $row)
{
    $this->dataStore("xyz")->append($row);
}
Eugene commented on Jul 5, 2019

ok thank you so much

MarkoS commented on Jan 11, 2021

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 just Join 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.

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
solved
suggestion

None