Question 209112 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Query With sqlsrv

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’

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    $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));

  2. 0

    I think joining this tables make things simpler:

    $builder->select('support_tickets.*')
    ->leftJoin('ticket_activities', 'support_tickets.id', '=', 'ticket_activities.ticket_id')
    ->orderByRaw('COALESCE(MAX(ticket_activities.created_at), support_tickets.created_at) DESC')
    ->paginate(Arr::get($setting, 'perPage', 40));

    SQL Server COALESCE() Function Return the first non-null value in a list.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search