PHP Laravel extract data from db by month & year

Question

Hello i have this function which sums the budget_cost and get the data by month

public function marktingCost(){

$costs = \DB::table('campaigns')
    ->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month'))
    ->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year'))
    ->groupBy('campaign_leadsource_id')
    ->where('campaign_status_id',4) // campaign_status_id = 4 means campaign completed
    ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month)
    ->get(); return $costs}

what im trying to achive is get the data by month as budget_total_month and get the data by year as budget_total_year but i can't use if condition inside query i want to do something like this

->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month') ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month))
->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year') ->where(\DB::raw('Year(created_at)'), Carbon::today()->year))

But of course that's not valid

what i want as output is that

[{"campaign_leadsource_id":1,"budget_total_month":11475,"budget_total_year":134761,"olxTotal":12,"budget_per_lead":11230},{"campaign_leadsource_id":2,"budget_total_month":4221,"budget_total_year":41215,"olxTotal":9,"budget_per_lead":4579}]

thank you in advance

I'm assuming that the month one is meant to be the month from just one year rather than the same month from every year? What version of Laravel are you using? — Rwd, Mar 15 '19 at 07:33
whereYear('created_at', '=', $year) ->whereMonth('created_at', '=', $month) — Palak Jadav, Mar 15 '19 at 07:35
Hello Thank you for you answer i tried it like this ` ->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_' . $Month)->whereMonth('created_at', '=', $Month)) ->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_' . $Year)->whereYear('created_at', '=', $year))` but i got "Call to undefined method Illuminate\Database\Query\Expression::whereMonth()" — mohamed adel, Mar 15 '19 at 07:59

score 1 · Answer 1 · answered Mar 15 '19 at 09:14

1

Please try this code.

$costs = \DB::table('campaigns')
        ->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month'))
        ->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year'))

        ->where('campaign_status_id',4) // campaign_status_id = 4 means campaign completed
        ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month)
        ->groupBy(\DB::raw("MONTH(created_at)"))
        ->get();

answered Mar 15 '19 at 09:14

Mayank Dudakiya

3,635
35
35

Thank you for your answer i tried it but got this error `SQLSTATE[42000]: Syntax error or access violation: 1055 'sedracrm.campaigns.campaign_leadsource_id' isn't in GROUP BY (SQL: select `campaign_leadsource_id`, SUM(budget_cost) as budget_total_month, `campaign_leadsource_id`, SUM(budget_cost) as budget_total_year from `campaigns` where `campaign_status_id` = 4 and MONTH(created_at) = 3 group by MONTH(created_at))` – mohamed adel Mar 15 '19 at 09:47
2

Please do this. In config\database.php --> "mysql" array Set 'strict' => false to disable all. Or follow this https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error – Mayank Dudakiya Mar 15 '19 at 10:55
It works but it get the data by month only and not by month & year – mohamed adel Mar 15 '19 at 11:24
2

replace this `->groupBy(\DB::raw("MONTH(created_at)"),\DB::raw("YEAR(created_at)"))` But you should check this to change the code in good shape https://stackoverflow.com/questions/40529355/laravel-eloquent-group-by-month-year – Mayank Dudakiya Mar 15 '19 at 11:31
it didn't work but thank you so much for your efforts <3 – mohamed adel Mar 15 '19 at 11:45
2

It should work, I have already done this before with the same approach. Check this link : https://stackoverflow.com/questions/40529355/laravel-eloquent-group-by-month-year – Mayank Dudakiya Mar 15 '19 at 11:47

score 0 · Answer 2 · answered Mar 15 '19 at 09:08

0

I know you are using laravel classes/methods as an abstraction to SQL. But have you tried using PDO and actual SQL statements to retrieve the data?

answered Mar 15 '19 at 09:08

Ray

11
4

i am totaly new how can i achieve that ? can you give me a code example to begin with ? – mohamed adel Mar 15 '19 at 09:10
Sample PDO select statement can be seen at the bottom of this site https://www.w3schools.com/php/php_mysql_select.asp – Ray Mar 15 '19 at 09:39

PHP Laravel extract data from db by month & year

2 Answers2