CODEWITHSUNDEEP
phpdatabaselaraveleloquent
Bulfaitelo
Bulfaitelo

Reputation: 513

Group by month or week with Laravel eloquent

I have a project which has management of my investments, that is, it takes my daily statement and saves it in my project so that I can see how much it is yielding, in summary I have a table that are these values daily, I can already generate the information separated by day and it works perfectly (in fact it is a little more complex than this what I do but the basis is this);

But now it's getting bad to see when I get a very large date range for example 1 year, it lists every day, I wanted a way to group by month or week,

Thanks in advance for any help.

    $rows =  AtivosExtrato::select('titulo_id', DB::raw('SUM(valor_bruto_atual) AS valor'), 'data_imports.data_import as created_at')
    ->join('titulos','titulo_id', '=', 'titulos.id' )
    ->join('representantes','representante_id', '=', 'representantes.id' )
    ->join('data_imports','data_import_id', '=', 'data_imports.id' )
    ->where('user_id', Auth::user()->id) 
    ->whereBetween('data_imports.data_import', [$request->input('start_date'), $request->input('end_date')])
    ->groupBy('titulos.nome_titulo')
    ->groupBy('data_imports.data_import')
    ->orderBy('data_import')
    ->orderBy('titulos.nome_titulo')
    ->get();

Briefly explaining what each eloquent information is:

AtivosExtrato: model where the daily income information is;

1) join: foreign table for the names of the titles

2) join: foreign table for the broker's name

3) join: table that saves the date of the import and relates to the id in the asset tableExtrato, it has a function to reduce the weight in the time of the searches and to gain performance.

where: limiting to the user in question

WhereBetween: limiting to date range

1) groupBy: Grouping by titles

2) groupBy: grouping by date of import

Table structure:

ativos_extrato ativos_extratos ativos_extratos foreign key ativos_extratos Index

data_imports data_imports

Solution:

$rows =  AtivosExtrato::select(
            'titulo_id',
            DB::raw('SUM(valor_bruto_atual) AS valor'),
            'data_imports.data_import as created_at',
            DB::raw('WEEK(data_imports.data_import) AS weeknumber')
        )
        ->join('titulos','titulo_id', '=', 'titulos.id' )
        ->join('representantes','representante_id', '=', 'representantes.id' )
        ->join('data_imports','data_import_id', '=', 'data_imports.id' )
        ->where('user_id', Auth::user()->id)
        ->whereIn('ativos_extratos.data_import_id',
        DataImport::Select(DB::raw('max(ID)'))
            ->whereBetween('data_import',  [$request->input('start_date'), $request->input('end_date')])
            ->groupBy(db::raw('Week(data_import)'))            )
            ->whereBetween('data_imports.data_import', [$request->input('start_date'), $request->input('end_date')])
        ->groupBy('titulos.nome_titulo')
        ->groupBy('weeknumber')            
        ->orderBy('data_import')            
        ->orderBy('titulos.nome_titulo')
        ->get();

Upvotes: 1

Views: 4673

Answers (1)

Alexey
Alexey

Reputation: 3484

Add DB::raw(WEEK(data_imports.data_import) AS weeknumber) and then replace ->groupBy('data_imports.data_import') with ->groupBy('weeknumber') and the same with MONTH() function if you want to group by month: add another select column DB::raw(MONTH(data_imports.data_import) AS monthnumber) and replace ->groupBy('data_imports.data_import') with ->groupBy('monthnumber'). So the whole Eloquent query with week grouping would be:

$rows =  AtivosExtrato::select('titulo_id', DB::raw('SUM(valor_bruto_atual) AS valor'), 'data_imports.data_import as created_at', DB::raw('WEEK(data_imports.data_import) AS weeknumber'))
->join('titulos','titulo_id', '=', 'titulos.id' )
->join('representantes','representante_id', '=', 'representantes.id' )
->join('data_imports','data_import_id', '=', 'data_imports.id' )
->where('user_id', Auth::user()->id) 
->whereBetween('data_imports.data_import', [$request->input('start_date'), $request->input('end_date')])
->groupBy('titulos.nome_titulo')
->groupBy('weeknumber')
->orderBy('data_import')
->orderBy('titulos.nome_titulo')
->get();

Upvotes: 2

Related Questions