Hi all,

I am using LeftJoin to merge two dataStores and display graph as attached below.

Simple example of query

   $myData= $this->src('automaker')
                ->query("SELECT
                	Datum,
                	Kolicina,
                	PrometAktuelna,
                	Dobavljac 
                FROM
                	my_table
                LIMIT 12                
                ");              
               
                
                $myData->pipe($this->dataStore("spojene_trenutna")); 

And I join data in the View with:

$new_store = $this->dataStore('spojene_star_pret')->leftJoin($this->dataStore('spojene_trenutna'),array(
                    "Datum"=>"Datum"
                ));

The thing is that query above (year 2021) does not return all 12 entries (12 months data) and it contains only (as marked with arrow on image below) Jan data. For that reason, graph does not show names of all months. You can see "null" on the side. Only Jan is display.

I am wondering how to fix that? I was thinking to re-create $myData and inject missing months, values with something like this:

$aktuelna = array(
  array( "Datum" => "Jan", "Kolicina" => 23135, "PrometAktuelna" => 2224.96, "Dobavljac" => 1675 ),
  array( "Datum" => "Feb", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Mar", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Apr", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
                   // ....
                   // .....
);
                

I have tried but does not work with this array. I believe there is easier way to get desired result.

Yeah I was thinking that. But not really sure how to "map" them together. In the way to make static array of data like below:

$blankArray= array(
  array( "Datum" => "Jan", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0),
  array( "Datum" => "Feb", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Mar", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Apr", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "May", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Jun", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Jul", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Aug", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Sep", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Oct", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Nov", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
  array( "Datum" => "Dec", "Kolicina" => 0, "PrometAktuelna" => 0.00, "Dobavljac" => 0 ),
);

And than update it's values by index if data is found in $myData store:

$myData->pipe($this->dataStore("spojene_trenutna"));

I would end up with merged data in data store __ $this->dataStore("data_merged") __ which should contain all "Datum" values (month names) and "kolicina", "PrometAktuelna" left as 0.00 if hasn't been changed. Later on I can use LeftJoin to connect with other data etc.

I am not quiet sure how to make this work to be honest. Would you be able to help?

Thanks for reply. It does makes difference, but does not work as expected.

Here is my setup:

$stara = $this->src('automaker')
                ->query("SELECT
                    dashboard_stara 
                ")
                ->params($query_params);
                
                $prethodna = $this->src('automaker')
                ->query("SELECT
                    dashboard_prethodna 
                ")
                ->params($query_params);
                
                // connect first two into one
                $stara_prethodna = new Join($stara,$prethodna,array("Datum"=>"Datum")); 
                $stara_prethodna->pipe($this->dataStore("spojene_star_pret")); 
                
                
                $this->src('automaker')
                ->query("SELECT
                	dashboard_aktuelna
                ")
                ->params($query_params)
                ->pipe($this->dataStore("spojene_trenutna"));

I've cut out query as it isn't important. I am joining two datastores inside Setup function, and third is passed over together in view. Than I run LeftJoin like below. That's only way I found to get this sort of data. As you can see, query runs three different tables. Than result have to merged and displayed in graph.

I have this in view:

$new_store = $this->dataStore('spojene_star_pret')->leftJoin($this->dataStore('spojene_trenutna'),array(
                    "Datum"=>"Datum"
                ));

I have tried this in the view:

$months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];

                //Create blank result array in associate format
                $result = [];
                foreach($months as $month) {
                    $result[$month] = array(
                        "Datum" => $month, 
                        "Kolicina" => 0, 
                        "PrometAktuelna" => 0.00, 
                        "Dobavljac" => 0
                    );
                }
                
                //Fill in the real data
                foreach($this->dataStore("spojene_trenutna") as $row) {
                    $result[$row["Datum"]] = $row;
                }
                
                //Convert associate array to normal array
                $result_filled = array_values($result);
                
    
                $new_store = $this->dataStore('spojene_star_pret')->leftJoin($result_filled,array(
                    "Datum"=>"Datum"
                ));


            BarChart::create(array(            
            "dataStore"=>$new_store,
            //....

Columns:

        "columns"=>array(
                
                "Datum",
                "Kolicina"=>array(
                    "label"=>"Količina 2021 (kom)",
                    "type"=>"number",
                ),
                "PrometAktuelna"=>array(
                    "label"=>"Promet 2021 (KM)",
                    "type"=>"number",
                    
                ),
                "PrometPrethodna"=>array(
                    "label"=>"Promet 2020 (KM)",
                    "type"=>"number",
                    
                ),
                "PrometStara"=>array(
                    "label"=>"Promet 2019 (KM)",
                    "type"=>"number",
                   
                        
                )
            )

But does not work :(

Yeeeee! :D :D Sorted :D Ohh excellent! And I've learned something today. Using new datastore like that to get proper format. Nice!

new \koolreport\core\DataStore($result_filled),

Thanks a lot!