Follow-up #27: mergeBindings not working

[rajansharma3097]

Hi Jonas,

Follow-up regarding #27, I've tried above and the following is the response: array:31 [ 0 => {#1343 +"created_date": "2021-09-03" +"successCalls": "0" +"failedCalls": "0" } 1 => {#1344 +"created_date": "2021-09-04" +"successCalls": "0" +"failedCalls": "0" } 2 => {#1345 +"created_date": "2021-09-05" +"successCalls": "0" +"failedCalls": "0" } . . .]

But when I run the actual code, then got only: array:1 [ 0 => array:3 [ "created_date" => "2021-10-12" "successCalls" => "0" "failedCalls" => "0" ] ]

Following is the Actual Code:

$subQuery = DB::table('tbl_calls')->selectRaw("DATE_FORMAT(created_at,'%d-%M-%Y') AS created_date, SUM(IF((dial_call_status = 'completed' OR dial_call_status = 'recording'), 1, 0)) AS successCalls, SUM(IF(dial_call_status = 'no-answer', 1, 0)) AS failedCalls") ->where([ // ['user_id', $userId], ['created_at', '<=', $startDate], ['created_at', '>=', $endDate], // ['campaign_name', 'like', "%$search%"] ]) ->unionAll( DB::table('date_cte') ->selectRaw('created_date, 0, 0') ) ->groupBy('created_date');

TblCalls::withRecursiveExpression('date_cte', function($query) use ($startDate, $endDate) { $query->selectRaw("CAST('$startDate' AS DATE) AS created_date") ->unionAll( DB::table('date_cte') ->selectRaw("ADDDATE(created_date, INTERVAL 1 DAY)") ->where([['created_date', '<', "'$endDate'"]]) ); }) ->selectRaw("created_date, successCalls,failedCalls") // ->from(DB::raw(' ( ' . $subQuery->toSql() . ' ) AS X ')) ->fromSub($subQuery, 'X') ->groupBy('created_date') ->get()->toArray();

Please help. Sorry for the late response.

[staudenmeir]

Please provide a database dump of all the tables involved so I can test it on my machine.

[rajansharma3097]

Hi,

Here is the table that is being used in the query: https://www.dropbox.com/s/wttz910k2wjj8av/tbl_calls.sql?dl=0

[staudenmeir]

Please also provide some sample data.

[rajansharma3097]

Jonas, this is what I want. If there is no data for a specific date, then rows must be fetched according to the date range specified in the query.

[staudenmeir]

I need an SQL dump of some sample rows that let me reproduce the issue.

[rajansharma3097]

Please just run the following query and it will return the rows as intended even without having any actual row in the table, but when we execute the same query from Laravel, it's returning only one row instead of multiple.

with recursive date_cte as ((select CAST('2021-09-03' AS DATE) AS created_date) union all (select ADDDATE(created_date, INTERVAL 1 DAY) from date_cte where (created_date < '2021-10-03'))) select created_date, successCalls,failedCalls from ((select DATE_FORMAT(created_at,'%d-%M-%Y') AS created_date, SUM(IF((dial_call_status = 'completed' OR dial_call_status = 'recording'), 1, 0)) AS successCalls, SUM(IF(dial_call_status = 'no-answer', 1, 0)) AS failedCalls from tbl_calls where (created_at <= '2021-09-03' AND created_at >= '2021-10-03') group by created_date) union all (select created_date, 0, 0 from date_cte)) as X

[staudenmeir]

What's the Laravel version of this query?

[rajansharma3097]

Here it is:

$subQuery = DB::table('tbl_calls')->selectRaw("DATE_FORMAT(created_at,'%d-%M-%Y') AS created_date, SUM(IF((dial_call_status = 'completed' OR dial_call_status = 'recording'), 1, 0)) AS successCalls, SUM(IF(dial_call_status = 'no-answer', 1, 0)) AS failedCalls") ->where([ ['created_at', '<=', $startDate], ['created_at', '>=', $endDate], ]) ->unionAll( DB::table('date_cte') ->selectRaw('created_date, 0, 0') ) ->groupBy('created_date');

TblCalls::withRecursiveExpression('date_cte', function($query) use ($startDate, $endDate) { $query->selectRaw("CAST('$startDate' AS DATE) AS created_date") ->unionAll( DB::table('date_cte') ->selectRaw("ADDDATE(created_date, INTERVAL 1 DAY)") ->where([['created_date', '<', "'$endDate'"]]) ); }) ->selectRaw("created_date, successCalls,failedCalls") ->fromSub($subQuery, 'X') ->groupBy('created_date') ->get()->toArray();

[staudenmeir]

As I already pointed out in https://github.com/staudenmeir/laravel-cte/issues/27#issuecomment-932899238, the difference and issue is the last GROUP BY clause: If you remove it from the Laravel query, you get the correct SQL and query results.

[rajansharma3097]

Hi Jonas,

Actually, the problem is with the toArray() method. I really appreciate your help. Thank you...