Table widget

Hello, I am using the table widget and a custom query that is grouping data:

return $table
            ->heading('')
            ->defaultSort('start', 'desc')
            ->emptyStateHeading('No calls found')
            ->paginated([5, 10, 25, 50])
            ->query(
                Cdr::where('dcontext', 'LIKE', $this->company->context() . '%')
                    ->whereNotIn('lastapp', ['Hangup', 'Read', 'Playback', 'VoiceMail'])
                    ->groupBy('uniqueid')
                    ->select(
                        'uniqueid',
                        DB::raw('MIN(start) as start'),
                        DB::raw('IFNULL((SELECT GROUP_CONCAT(DISTINCT CONCAT(extensions.display_name, " <", cdr.vsip, ">")) AS vsip FROM cdr c2 WHERE c2.uniqueid = cdr.uniqueid AND answered="Yes" LIMIT 1), "-") as vsip'),
                        DB::raw('MAX(vclid) as vclid'),
                        DB::raw('MAX(vdst) as vdst'),
                        DB::raw('MAX(cdr.vdirection) as vdirection'),
                        DB::raw('sec_to_time(timediff(MAX(answer), MIN(start))) as vwait'),
                        DB::raw('sec_to_time(SUM(cdr.duration)) as vduration'),
                        DB::raw('IFNULL((SELECT answered FROM cdr c2 WHERE c2.uniqueid = cdr.uniqueid AND answered="Yes" LIMIT 1), "No") AS answered'),
                        DB::raw("MAX($recording) as recording"),
                        DB::raw("MAX(recordings.file) AS recording"),
                    )
                    ->join('pbx.extensions', 'number', '=', DB::raw('cdr.vsip COLLATE utf8mb4_unicode_ci'), 'left')
                    ->join('pbx.recordings', 'file', 'LIKE', DB::raw('CONCAT("%", cdr.uniqueid, "%") COLLATE utf8mb4_unicode_ci'), 'left')
                    ->limit(10)
            )

return $table
            ->heading('')
            ->defaultSort('start', 'desc')
            ->emptyStateHeading('No calls found')
            ->paginated([5, 10, 25, 50])
            ->query(
                Cdr::where('dcontext', 'LIKE', $this->company->context() . '%')
                    ->whereNotIn('lastapp', ['Hangup', 'Read', 'Playback', 'VoiceMail'])
                    ->groupBy('uniqueid')
                    ->select(
                        'uniqueid',
                        DB::raw('MIN(start) as start'),
                        DB::raw('IFNULL((SELECT GROUP_CONCAT(DISTINCT CONCAT(extensions.display_name, " <", cdr.vsip, ">")) AS vsip FROM cdr c2 WHERE c2.uniqueid = cdr.uniqueid AND answered="Yes" LIMIT 1), "-") as vsip'),
                        DB::raw('MAX(vclid) as vclid'),
                        DB::raw('MAX(vdst) as vdst'),
                        DB::raw('MAX(cdr.vdirection) as vdirection'),
                        DB::raw('sec_to_time(timediff(MAX(answer), MIN(start))) as vwait'),
                        DB::raw('sec_to_time(SUM(cdr.duration)) as vduration'),
                        DB::raw('IFNULL((SELECT answered FROM cdr c2 WHERE c2.uniqueid = cdr.uniqueid AND answered="Yes" LIMIT 1), "No") AS answered'),
                        DB::raw("MAX($recording) as recording"),
                        DB::raw("MAX(recordings.file) AS recording"),
                    )
                    ->join('pbx.extensions', 'number', '=', DB::raw('cdr.vsip COLLATE utf8mb4_unicode_ci'), 'left')
                    ->join('pbx.recordings', 'file', 'LIKE', DB::raw('CONCAT("%", cdr.uniqueid, "%") COLLATE utf8mb4_unicode_ci'), 'left')
                    ->limit(10)
            )

How can I limit the query, since limit is not working?

1 Reply

toeknee•3w ago

Wouldn

->model(Cdr::class)
->query(fn($query) => 
                $query->where('dcontext', 'LIKE', $this->company->context() . '%')
                    ->whereNotIn('lastapp', ['Hangup', 'Read', 'Playback', 'VoiceMail'])
                    ->groupBy('uniqueid')
                    ->select(
                        'uniqueid',
                        DB::raw('MIN(start) as start'),
                        DB::raw('IFNULL((SELECT GROUP_CONCAT(DISTINCT CONCAT(extensions.display_name, " <", cdr.vsip, ">")) AS vsip FROM cdr c2 WHERE c2.uniqueid = cdr.uniqueid AND answered="Yes" LIMIT 1), "-") as vsip'),
                        DB::raw('MAX(vclid) as vclid'),
                        DB::raw('MAX(vdst) as vdst'),
                        DB::raw('MAX(cdr.vdirection) as vdirection'),
                        DB::raw('sec_to_time(timediff(MAX(answer), MIN(start))) as vwait'),
                        DB::raw('sec_to_time(SUM(cdr.duration)) as vduration'),
                        DB::raw('IFNULL((SELECT answered FROM cdr c2 WHERE c2.uniqueid = cdr.uniqueid AND answered="Yes" LIMIT 1), "No") AS answered'),
                        DB::raw("MAX($recording) as recording"),
                        DB::raw("MAX(recordings.file) AS recording"),
                    )
                    ->join('pbx.extensions', 'number', '=', DB::raw('cdr.vsip COLLATE utf8mb4_unicode_ci'), 'left')
                    ->join('pbx.recordings', 'file', 'LIKE', DB::raw('CONCAT("%", cdr.uniqueid, "%") COLLATE utf8mb4_unicode_ci'), 'left')
)

->model(Cdr::class)
->query(fn($query) => 
                $query->where('dcontext', 'LIKE', $this->company->context() . '%')
                    ->whereNotIn('lastapp', ['Hangup', 'Read', 'Playback', 'VoiceMail'])
                    ->groupBy('uniqueid')
                    ->select(
                        'uniqueid',
                        DB::raw('MIN(start) as start'),
                        DB::raw('IFNULL((SELECT GROUP_CONCAT(DISTINCT CONCAT(extensions.display_name, " <", cdr.vsip, ">")) AS vsip FROM cdr c2 WHERE c2.uniqueid = cdr.uniqueid AND answered="Yes" LIMIT 1), "-") as vsip'),
                        DB::raw('MAX(vclid) as vclid'),
                        DB::raw('MAX(vdst) as vdst'),
                        DB::raw('MAX(cdr.vdirection) as vdirection'),
                        DB::raw('sec_to_time(timediff(MAX(answer), MIN(start))) as vwait'),
                        DB::raw('sec_to_time(SUM(cdr.duration)) as vduration'),
                        DB::raw('IFNULL((SELECT answered FROM cdr c2 WHERE c2.uniqueid = cdr.uniqueid AND answered="Yes" LIMIT 1), "No") AS answered'),
                        DB::raw("MAX($recording) as recording"),
                        DB::raw("MAX(recordings.file) AS recording"),
                    )
                    ->join('pbx.extensions', 'number', '=', DB::raw('cdr.vsip COLLATE utf8mb4_unicode_ci'), 'left')
                    ->join('pbx.recordings', 'file', 'LIKE', DB::raw('CONCAT("%", cdr.uniqueid, "%") COLLATE utf8mb4_unicode_ci'), 'left')
)

Work?

Gaming

Programming

Table widget