KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

How to do LEFT JOIN? #970

Open Eugene opened this topic on on Jul 4 - 13 comments

Eugene commented on Jul 4

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

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

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

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

You use process() method of DataStore.

Eugene commented on Jul 5

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

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

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

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

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

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

Eugene commented on Jul 5

I meant merge rows... like union all

KoolReport commented on Jul 5

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

ok thank you so much

Give tips to supporter for his good work

If you feel that supporter has done a good work, consider giving him some credit. Any amount put into the tips box below is appreciated. By doing so, you have contribtuted to the existence of KoolReport and the quality of support.

Tips box
solved
suggestion

None