KoolReport's Forum

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

Query is working on MySQL Workbench but columns are blank in the report #1031

Closed paulo opened this topic on on Aug 6 - 5 comments

paulo commented on Aug 6

Hi All, I have a query that works on mysql and shows the right results, however when I plug in the report the columns client and tourname are blank.

  $this->src('mysql')->query("SELECT  act.Type, act.Due_date, act.Subject, T.number, T.name as tour, C.name as client, act.Status, act.notes,  
                       concat_WS(' ' ,T.number, ' ',T.name) as tourname 
                         FROM activities as act
                         left join tours as T on T.id = act.activatable_id AND act.activatable_type='App\\Tour' 
                         left join clients as C on C.id = act.activatable_id AND act.activatable_type='App\\Client'
                         WHERE
                          act.Status!='Completed' AND act.staff_id=$staff_id
                         order by act.Due_date ASC;    ")
        ->pipe($this->dataStore('searchableActivities'));

VIEW:

   <?php
    DataTables::create(array(
        //"dataSource"=>$this->dataStore("searchableActivities"),
        "dataStore" => $this->dataStore("searchableActivities"),
        "options"=>array(
            "searching"=>true,
            "fixedHeader"=>true
           // "paging"=>true,
        ),
        "columns"=>array(
            "Type",
            "Due_date",
            "Subject",
            "tourname"=>array(
                "type"=>"text",
                "label"=>"Tours",
            ),
            "client",
            "Status",
            "notes"

        )
    ));
    ?>

thank you

KoolReport commented on Aug 7

I see that you have act.staff_id=$staff_id in your SQL, do you define the $staff_id? If it is null then it will cause error.

paulo commented on Aug 7

yes, I'm passing the id to the report: $report = new MyActivitiesReport(array(

                "userId"=>Auth::user()->id
            ));
            $report->run();

(Laravel).

On the report class, using debug I can see the number: "MyActivitiesReport this staff id=1"

 function setup()
    {

        Log::debug("MyActivitiesReport this id=".$this->params["userId"] );
        $id = $this->params["userId"]; //Auth::user()->id;
        $staff_id = Staff::where(['user_id' => $id])->value('id');
        Log::debug("MyActivitiesReport this staff id=".$staff_id );

When replacing in MySQL $staff_id with 1 it works and brings results back with all columns. thank you

KoolReport commented on Aug 7

Let say if you input directly "act.staff_id=1" in the SQL statement in the report, also remove the ";" at the end of SQL statement. Does it work?

paulo commented on Aug 8

thank you for replying. I tried with 1 and removing and keeping the ";". The columns are still coming blank. It is very strange because it works inside of Workbench which makes me believe could be a potential bug in the reporting system ? I even tried just this in the view and it didn't work: <?php

DataTables::create(array(
  //  "dataSource"=>$this->dataStore("searchableActivities"),
    "dataStore" => $this->dataStore("searchableActivities"),

    "options"=>array(
        "searching"=>true
    )
));
?>

thanks

paulo commented on Sep 17

I FOund the issue here ! The string needs to have triple \ so act.activatable_type='App\\Tour'

left join tours as T on T.id = act.activatable_id AND act.activatable_type='App\\Tour'

                     left join clients as C on C.id = act.activatable_id AND act.activatable_type='App\\\Client'

KoolReport Is Free and Open-Source!

KoolReport is a professional php reporting framework which saves you tons of time to construct dynamic data report & dashboard.

  • Connect to various datasources such as MySQL, SQL Server, MongoDB or even from CSV or Excel file.
  • Contain series of powerful built-in data processes for your data manipulation.
  • Generate stunning charts and graphs to help you communicate data insights to your audiences effectively.
  • Integrate seamlessly with any php frameworks such as Laravel, CodeIgniter, Symfony.
Download Now and register our tutorials to get started!

Download KoolReport
bug
help needed

None