KoolReport's Forum

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

Joining a csv datasource with a mySQL datasource #1608

Open Richb201 opened this topic on on Sep 2 - 9 comments

Richb201 commented on Sep 2

I have a datastore that I am loading from a csv file.

      "contemporaneous_datasource"=>array(
            "class"=>'\koolreport\datasources\CSVDataSource',
            "filePath"=>"/app/assets/output/contemporaneous_Applerichb201@gmail.com",
            "fieldSeparator"=>",",
                                            ),

I want to join this with a datastore that comes from a mySQL table

       $employee_source = $this->src('employee_source')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");
        $join = new Join("contemporaneous_datasource",$employee_source,array("email"=>"employee_email"));
        $join->pipe($this->dataStore('together'));

The index is "email" in the csv file and "employee_email" in the mySQL table. I am getting an error Datasource not found "employee_source". What am I doing wrong?

David Winterburn commented on Sep 3

Please try this and let us know if it works for you:

$employee_source = $this->src('employee_source')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");
$contemporaneous_datasource = $this->src("contemporaneous_datasource");
$join = new Join($contemporaneous_datasource, $employee_source, array("email"=>"employee_email"));
Richb201 commented on Sep 3

I am getting this error to your updated code: An uncaught Exception was encountered Type: Exception

Message: Datasource not found 'employee_source'

Filename: /app/vendor/koolreport/core/src/KoolReport.php

Line Number: 255

Backtrace:

File: /app/assets/MyReport.php Line: 115 Function: src

File: /app/vendor/koolreport/core/src/KoolReport.php Line: 100 Function: setup

File: /app/application/controllers/Configure.php Line: 2439 Function: __construct

File: /app/index.php Line: 315 Function: require_once

Richb201 commented on Sep 3

And then I tried:

    $employee_source = $this->src('substantiator')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");
    $join = new Join("contemporaneous_datasource",$employee_source,array("email"=>"employee_email"));
    $join->pipe($this->dataStore('together'));

I get this in the log file: ERROR - 2020-09-03 19:58:39 --> Severity: error --> Exception: Call to a member function pipe() on string /app/vendor/koolreport/core/src/processes/Join.php 62

I sure do hope you guys have an easy solution because this "trial and error" is killing me.

David Winterburn commented on Sep 4

Please replace "employee_source" with "substantiator". I'm not an oracle to know you changed your data source name "employee_source" in the 1st post with "substantiator" in your latest one:

$employee_source = $this->src('substantiator')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");

If you look carefully, there's differences between my code and your code:

$contemporaneous_datasource = $this->src("contemporaneous_datasource");
$join = new Join($contemporaneous_datasource, $employee_source, array("email"=>"employee_email"));

Why using your same old code and then complaining ""trial and error" is killing me"? Please be careful with your code:

$employee_source = $this->src('substantiator')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");
$contemporaneous_datasource = $this->src("contemporaneous_datasource");
$join = new Join($contemporaneous_datasource, $employee_source, array("email"=>"employee_email"));
Richb201 commented on Sep 4

David, I pasted your code exactly as you entered it in the above response into setup(). I did modify it to "substantiator" at one point, to try to get your code working (and commented it). Here are the 3 versions I have tried so far. Notice that they are commented out since none of the 3 worked. Notice that the top one attempts to use "employee_source".

/*
        $employee_source = $this->src('employee_source')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");
        $contemporaneous_datasource = $this->src("contemporaneous_datasource");
        $join = new Join($contemporaneous_datasource, $employee_source, array("email"=>"employee_email"));
*/
/*  from 'sample' code on website
        $employee_source = $this->src('substantiator')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");
        $join = new Join("contemporaneous_datasource",$employee_source,array("email"=>"employee_email"));
        $join->pipe($this->dataStore('together'));
*/
/* this was your initial suggestion
        $employee_source = $this->src('employee_source')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");
        $contemporaneous_datasource = $this->src("contemporaneous_datasource");
        $join = new Join($contemporaneous_datasource, $employee_source, array("email"=>"employee_email"));
*/

Just to be clear, I am showing the settings

    function settings()
    {
//        $szString=$_SESSION['campaign'].$_SESSION['userid'];
        return array(
            "dataSources"=>array(
                "substantiator" => array(
                    "connectionString" => "mysql:host=mysql;dbname=substantiator",
                    "username" => "admin",
                    "password" => "xxx",
                    "charset" => "utf8"
                                        ),

            "activity_datasource"=>array(
                "class"=>'\koolreport\datasources\CSVDataSource',
          //      "filePath"=>"/app/assets/output/activity_".$_SESSION['campaign'].$_SESSION['userid'],
                "filePath"=>"/app/assets/output/activity_Applerichb201@gmail.com",
                "fieldSeparator"=>",",
                                        ),
            "bus_comps_datasource"=>array(
                "class"=>'\koolreport\datasources\CSVDataSource',
                "filePath"=>"/app/assets/output/buscomps_Applerichb201@gmail.com",
                "fieldSeparator"=>",",
                                        ),
            "contemporaneous_datasource"=>array(
                "class"=>'\koolreport\datasources\CSVDataSource',
                "filePath"=>"/app/assets/output/contemporaneous_Applerichb201@gmail.com",
                "fieldSeparator"=>",",
                                                ),
            )
        );
    }

Additionally, here is your code and the error it causes:

$employee_source = $this->src('employee_source')->query("SELECT employee, w2_wages, employee_email FROM employees WHERE email='$email' AND campaign= '$_SESSION[campaign]' ");
$contemporaneous_datasource = $this->src("contemporaneous_datasource");
$join = new Join($contemporaneous_datasource, $employee_source, array("email"=>"employee_email"));

An uncaught Exception was encountered
Type: Exception

Message: Datasource not found 'employee_source'

Filename: /app/vendor/koolreport/core/src/KoolReport.php

Line Number: 255

Backtrace:

File: /app/assets/MyReport.php
Line: 125
Function: src

File: /app/vendor/koolreport/core/src/KoolReport.php
Line: 100
Function: setup

File: /app/application/controllers/Configure.php
Line: 2439
Function: __construct

File: /app/index.php
Line: 315
Function: require_once
David Winterburn commented on Sep 4

All your 3 codes are wrong because:

  • The ones with the correct structure (1 and 3) use wrong data source name.
  • The one with the correct data source name (2) uses wrong structure.

Do you see that?

Richb201 commented on Sep 4

OK. I got it to run by changing the name of the database as requested. Now I am in the .view. I have named the join $contemp_join. It is a combo of the CSV and the employee table.

Table::create(array(
    "dataStore"=>$this->dataStore("contemp_join"),
    "showFooter"=>true,
    "columns"=>array(
        "employee",
        "activity",
        "business_component",
        "project",
        "total hours",
        "qualified hours",
        "rate",
        "total $"
        ),
    )
);

Some of these fields are from the CSV and some are from the table. When I run this I get "no data available in table". I am going to check to make sure that the field names are available at least in one of the two datastores! I will need to do some SUM() so I will need to do a pipe after the join. Any problem with doing that? As an alternative,as I have told you before, I prefer to work in SQL. Can I write SQL statements that will work on the joined datastore?

Thx for your help!

KoolReport commented on Sep 7

You may not able to use SQL on joined datastore. If you really want to use SQL to join, you could import your csv to MySQL.

Richb201 commented on Sep 7

Thank you. Is there any function in koolreport to allow me import csv?

Also, does kool support multiple joins? How about outer joins?

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
None yet

None