SQL Query TO Laravel Query Builder Format

Can any body help me to convert this raw sql query to laravel query builder syntax

`` SELECT YEAR(matches.init_ts) AS YEAR , MONTH(matches.init_ts) AS MONTH, WEEK(matches.init_ts) AS WEEK , DAY(matches.init_ts) AS DAY , AVG(stat_value) AS avg_stat , player_id AS Id, 'player' AS NAME

FROM matches_stats JOIN matches ON matches.id = matches_stats.match_id WHERE player_id = 1 AND matches_stats.stat_type_id = 11 AND WEEK(matches.init_ts) = WEEK(NOW()) GROUP BY YEAR(matches.init_ts),MONTH(matches.init_ts),player_id,WEEK(matches.init_ts),DAY(matches.init_ts) ``

rodrigo.pedra

5 years ago

Level 56

Tip: use 3 backticks (```) or three tildes (~~~) around your code block to make it pretty-formatted. This backticks\tildes sequence should live in their own line.

For example:

```
if ($foo) return 'ok';
```

Or:

~~~
if ($foo) return 'ok';
~~~

rodrigo.pedra

5 years ago

Level 56

Here you go:

$record = \Illuminate\Support\Facades\DB::table('matches_stats')
    ->join('matches', 'matches.id', '=', 'matches_stats.match_id')
    ->selectRaw('YEAR(matches.init_ts) AS YEAR')
    ->selectRaw('MONTH(matches.init_ts) AS MONTH')
    ->selectRaw('WEEK(matches.init_ts) AS WEEK')
    ->selectRaw('DAY(matches.init_ts) AS DAY')
    ->selectRaw('AVG(stat_value) AS avg_stat')
    ->selectRaw('player_id AS Id')
    ->selectRaw("'player' AS NAME")
    ->where('player_id', 1)
    ->where('matches_stats.stat_type_id', 11)
    ->whereRaw('WEEK(matches.init_ts) = WEEK(NOW())')
    ->groupByRaw('YEAR(matches.init_ts)')
    ->groupByRaw('MONTH(matches.init_ts)')
    ->groupBy('player_id')
    ->groupByRaw('WEEK(matches.init_ts)')
    ->groupByRaw('DAY(matches.init_ts)')
    ->get(); // execute query

Please or to participate in this conversation.