phpmyadmin Question posted in
The official documentation can be found here.

Laravel Query builder not fetch any response but same query work fine in MySql – Phpmyadmin

I am trying to Run This Query from laravel Query Builder But it returns an empty response
but when I get the query from the query log and run this in phpMyadmin it response with data.

 Project::join('project_assignation','projects.id','project_assignation.project_id')
        ->join('users','project_assignation.employee_id', 'users.id')
         ->join('eods', 'eods.user_id', 'users.id')
         ->select('projects.project_name', DB::Raw('SUM(eods.today_hours) as pr_time'))
         ->where('users.id', 210)
         ->whereMonth('eods.date', 9)
         ->whereYear('eods.date', 2019)
         ->where('eods.project_id', '=', 'projects.id')
         ->groupBy('projects.project_name')
         ->orderBy('projects.project_name')
         ->get();

Query Log:

 Array (
     [0] => Array
         (
             [query] => select `projects`.`project_name`, SUM(eods.today_hours) as pr_time from `projects` inner join
 `project_assignation` on `projects`.`id` =
 `project_assignation`.`project_id` inner join `users` on
 `project_assignation`.`employee_id` = `users`.`id` inner join `eods`
 on `eods`.`user_id` = `users`.`id` where `users`.`id` = ? and
 month(`eods`.`date`) = ? and year(`eods`.`date`) = ? and
 `eods`.`project_id` = ? group by `projects`.`project_name` order by
 `projects`.`project_name` asc
             [bindings] => Array
                 (
                     [0] => 210
                     [1] => 9
                     [2] => 2019
                     [3] => projects.id
                 )

             [time] => 0.98
         )

 )

In php myadmin this query execute fine and also return related data.

enter image description here

Tags:

2

Answers


  1. 0

    Just from first view I saw that in your SQL you didn’t have comparison of “eods.project_id” with “project.id”, which you had in Query Builder:

     ->where('eods.project_id', '=', 'projects.id')

    So I think this answer can be useful for you

    Login or Signup to reply.
  2. 0

    With Laravel, the second/third parameter in a WHERE query is passed as a string, and would not be treated as a column name. Therefore, in a raw query, 

     ->where('eods.project_id', '=', 'projects.id')

    would look like

     WHERE `eods`.`project_id` = 'projects.id'

    You need to pass it in as a RAW query so that it would compare column to column

    ->whereRaw('eods.project_id = projects.id')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search