SQL Server Laravel Query With sqlsrv

wfveoks0 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(99)

i have a query to fetch all data and these data are support tickets, each support ticket has many comments, i want to order these data by last comment if exists else order by ticket created date

any idea what is the issue here and how i can fix it?

thanks in advance

$builder->select('support_tickets.*', DB::raw('(SELECT max(created_at) as latest_created_at FROM ticket_activities WHERE support_tickets.id = ticket_activities.ticket_id GROUP BY ticket_id) as latest'))
->orderByRaw('CASE WHEN EXISTS (SELECT max(created_at) as latest_created_at FROM ticket_activities WHERE support_tickets.id = ticket_activities.ticket_id GROUP BY ticket_id) THEN support_tickets.created_at ELSE latest END DESC')
            ->paginate(Arr::get($setting, 'perPage', 40));

it is return Invalid column name 'latest'

sql-server

来源：https://stackoverflow.com/questions/76698796/laravel-query-with-sqlsrv

1条答案

按热度按时间

zbq4xfa01#

$builder->->select('support_tickets.*')
            ->joinSub(function (Builder $builder) {
                $builder->select(
                    'ticket_id',
                    DB::raw('MAX(created_at) as latest_date')
                )
                    ->from('ticket_activities')
                    ->groupBy('ticket_id');
            }, 'latest_activity', function (JoinClause $join) {
                $join->on('support_tickets.id', '=', 'latest_activity.ticket_id');
            }, null, null , 'left');

            $builder = $builder->orderByRaw('COALESCE(latest_date, support_tickets.created_at) DESC');
            return $builder->paginate(Arr::get($setting, 'perPage', 40));

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Laravel Query With sqlsrv

1条答案

相关问题

热门标签

最新问答