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

Open paulo opened this topic on on Aug 6 - 4 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

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
bug
help needed

None