Querybuilder OrderBy Not Working On Laravel-CodePudding

order by is not working in the below command and I don't know where I made a mistake. I will be glad if you can help, thank you

$value = DB::table('users')
    ->select(['*', DB::raw('MAX(conver_date) AS lastConver')])
    ->leftJoin('conversation', function ($join) use ($user_id) {
        $join->on('conversation.conver_seller_id', '=', 'users.id')
            ->on('conversation.conver_user_id', '=', DB::raw($user_id))
            ->orOn('conversation.conver_seller_id', '=', DB::raw($user_id))
            ->on('conversation.conver_user_id', '=', 'users.id'); 
    })
    ->where('user_type', '!=', 'admin')
    ->where('id', '!=', $user_id)
    ->where('drop_status', '=', 'no')
    ->orderBy('conver_date', 'desc')
    ->groupBy('id')
    ->get(); 
    
return $value;

CodePudding user response：

It seems that the issue is with the orderBy clause. The column that you are trying to order by, "conver_date", is in the "conversation" table, but you are selecting it as "lastConver" in the select clause, so the orderBy clause should be updated to use "lastConver" instead of "conver_date".

Try updating your query like this:

$value = DB::table('users')
    ->select(['*', DB::raw('MAX(conver_date) AS lastConver')])
    ->leftJoin('conversation', function ($join) use ($user_id) {
        $join->on('conversation.conver_seller_id', '=', 'users.id')
            ->on('conversation.conver_user_id', '=', DB::raw($user_id))
            ->orOn('conversation.conver_seller_id', '=', DB::raw($user_id))
            ->on('conversation.conver_user_id', '=', 'users.id'); 
    })
    ->where('user_type', '!=', 'admin')
    ->where('id', '!=', $user_id)
    ->where('drop_status', '=', 'no')
    ->orderBy('lastConver', 'desc')
    ->groupBy('id')
    ->get(); 

return $value;

This should correctly order the results by the "lastConver" column, which is the MAX(conver_date) selected in your query.

CodePudding user response：

I think you need order by max value

->orderBy('lastConver', 'desc')

if it still not helping, edit in config/database.php mysql options set to strict => false