skip to Main Content

For some reason, when I put my raw MYSQL into PHPMYADMIN it works the way I expect it to. However, when I use Laravel (I cant see why it’s not working!) will give Syntax error or access violation error

Any help would be appreciated.

Laravel

$timings = DB::table('timings')
                    ->distinct()
                    ->selectRaw("employee_id, min(time_logged) AS minTime, max(time_logged) AS maxTime")
                    ->where('timings.employee_id' , $id)
                    ->groupBy('timings.employee_id', 'cast(time_logged AS date)' )
                    ->get();

Raw MYSQL

SELECT DISTINCT employee_id, min(time_logged) AS minTime, max(time_logged) AS maxTime  
FROM timings 
WHERE timings.employee_id = 1
GROUP BY timings.employee_id, cast(time_logged AS date)

2

Answers


    • First, make sure your credentials to access your database are correct(in your .env file.)
    • Second, you will need to use DB::raw() for expressions that operate on column name itself.

    So your query would look like:

    $timings = DB::table('timings')
                        ->distinct()
                        ->selectRaw("employee_id, min(time_logged) AS minTime, max(time_logged) AS maxTime")
                        ->where('timings.employee_id' , $id)
                        ->groupBy('timings.employee_id', DB::raw('cast(time_logged AS date)'))
                        ->get();
    
    Login or Signup to reply.
  1. Although, can’t say this is not recommended way but it’s definitely not Laravel-style.

    Let’s assume you have AppUser, and and AppTiming. Why don’t you connect them using hasOne, so, your table for timings looks like this:

    | login_time | logout_time | user_id |
    |------------|-------------|---------|
    | xx-xx PM | xx-xx PM | 1 |
    | xx-xx PM | xx-xx AM | 2 |

    Now you can easily make it, $user->timings->login_time 🙂

    Why are people afraid of relationships, you should use it anytime possible.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search