KoolReport's Forum

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

Merging multiple dataStore with groupby Into one single table (grouped by name) #1541

Open paulo opened this topic on on Jul 25 - 6 comments

paulo commented on Jul 25

Hi KoolReport folks, need your help figuring this out. I've 3 dataStores: #1

  $first = $this->src('mysql')->query($queryPlanning)
        ->pipe(new Group(array(
            "by"=>array("PlannerID","Planner"),
            "sum"=>"Balance"
        )))
        ->pipe($this->dataStore('query'));

#2 $second = $this->src('mysql')->query($queryQuoting)

        ->pipe(new Group(array(
         //   "by"=>"Quoter",
            "by"=>array("QuoterID","Quoter"),
            "sum"=>"BalanceQuoting"
        )))
        ->pipe($this->dataStore('queryQuoting'));

#3 $third = $this->src('mysql')->query($queryShared)

        ->pipe(new Group(array(
            "by"=>"SharedName",
            "sum"=>"Balance"
        )))
        ->pipe($this->dataStore('queryShared'));

Now, I am trying to join the tables. Expected result: A combination of both tables. All names will be listed independently if they are in both tables or not. I am trying this: $join = new Join($first,$second,array("PlannerID"=>"QuoterID"));

    $join->pipe($this->dataStore("result"));

I am getting nothing - result table is blank. It is not even showing the ids that are overlapping for some reason.

Example #1 data source 01 PB 1000 02 CC 2000 #2 data souce 01 PB 300 05 AA 5000

Expected results: 01 PB 1000 300 02 CC 2000 0 05 AA 0 5000

Any help is appreciated. thank you Paulo

paulo commented on Jul 25

I also tried

    $rp = $this->dataStore("query")->data();
    $rp1 = $this->dataStore("queryQuoting")->data();
    $final_table= array_merge_recursive($rp,$rp1);
<div style="margin-top:20px;">
    <?php
    Table::create(array(
        "dataSource"=>$this->dataStore("final_table"),
        "cssClass"=>array(
            "table"=>"table-bordered table-striped table-hover"
        )
    ));
    ?>
</div>

without success - thanks

David Winterburn commented on Jul 27

Please print screenshot of your datastores' data for us to check it. Thanks!

paulo commented on Jul 27

Hi David, thanks for getting back. I've sent you an email to support@koolreport.com with sample data (as I can't share here), and also added the full query and the queries for all three reports with some details.

thank you very much

Paulo

David Winterburn commented on Jul 28

Hi Paulo,

After carefully checking your code, I think that the Join process only works before the results are piped to a datastore. So your code should be like this:

    $first = $this->src('mysql')->query($queryPlanning)
        ->pipe(new Group(array(
            "by"=>array("PlannerID","Planner"),
            "sum"=>"Balance"
        )));

    second = $this->src('mysql')->query($queryQuoting)
        ->pipe(new Group(array(
            "by"=>array("QuoterID","Quoter"),
            "sum"=>"BalanceQuoting"
        )));

    $join = new Join($first,$second,array("PlannerID"=>"QuoterID"));
    $join->pipe($this->dataStore("result"));

Please let us know if this works for you. Thanks!

paulo commented on Jul 30

thanks. I tried that, it didn't do what was expecting but that is ok.. I think what I am looking for is really unique.
The first query returns a Balance total, the second returns a BalanceQuoting sum. I was trying to see if I could get PlannerName sum Balance, Sum balanceQuoting, but when I do the join trying to match the PlannerID and the Quoter ID , it doesn't bring the neither sum correct (Balance or BalanceQuote).
I think it is not fully taking into consideration the ->pipe(new Group(array(

        "by"=>array("PlannerID","Planner"),
        "sum"=>"Balance"
    )));

function before doing the join unfortunately.

thanks for trying. Paulo

paulo commented on Jul 30

I've sent an email with the report just in case if you want to play with this a bit more :) thank you

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