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

“How do I get a list of the latest messages between users in Laravel?”

I’m looking to get a list of all messages between users but only the latest message sent by either user A or user B in the database these fields are fromUser and toUser

I need a list of the latest message between each users.

so for example:

fromUser toUser message date (this would be a valid date iso time string)
1 2 "Hi there" 6 days ago
1 3 "Hello bro" 1 day ago
2 1 "Hi admin" 5 minutes ago

I want to get the following list

userPair message
2, 1 "Hi admin"
1, 3 "Hello bro"

but I want the userPair to only contain the other user and not my own user, my own user id would be 1

Edit 1

Yes, I’ve tried quite a few things

Example 1

$results = DB::table("messages")
        ->selectRaw('GREATEST("fromUser", "toUser") as user1, LEAST("fromUser", "toUser") as user2, MAX(created_at) AS maxdate')
        ->where('fromUser', $user->id)
        ->orWhere('toUser', $user->id)
        ->groupByRaw('GREATEST("fromUser", "toUser"), LEAST("fromUser", "toUser")')
        ->get()
        ->flatten();

Example 2

$results = ModelsMessages::where(function($query)use($user){
            $query->where('toUser', $user->id)->orWhere('fromUser', $user->id)->orderBy("created_at");
        })->select("toUser", "fromUser")->distinct()->get();

Example 3

 $results = DB::table('messages')
        ->select('*')
        ->where(function($query) use($user){
            ->where('fromUser', $user->id)
            ->orWhere('toUser', $user->id)
        })
        ->orderBy('created_at', 'desc')
        ->limit(1)
        ->unionAll(function ($query) use ($user) {
            $query->select('*')
                ->from('messages')
                ->orderBy('created_at', 'desc')
                ->limit(1);
        })
        ->get()->flatten();
Tags:

2

Answers


  1. 0

    You can run this query.

    auth()->user()->id is the id you want to filter in userPair .

    use DB;

Message::orderBy('date', 'desc')
  ->where(function($q){
    $q->where('fromUser', auth()->user()->id) 
      ->orWheere('toUser', auth()->user()->id);
  })
  ->select(
    'message', 
    DB::raw("IF(fromUser = ?, toUser, fromUser) as user", [auth()->user()->id]),
    'CONCATENATE(fromUser, ",", toUser) as users' // If you want to select the exact pair you have specified in question
  )
  ->get();
    Login or Signup to reply.
  2. 0

    If you can fix the partitioning properly, you can use a ranking function like ROW_NUMBER to order on your date column. Then it’s sufficient to filter out records that have a ranking higher than 1.

    WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER(
               PARTITION BY CASE WHEN fromuser > touser THEN fromuser ELSE touser END, 
                            CASE WHEN fromuser > touser THEN touser ELSE fromuser END
               ORDER     BY date) AS rn
    FROM tab
)
SELECT CONCAT(fromuser, ', ', touser) AS userpair, 
       message
FROM cte
WHERE rn = 1;

    Output:

    userpair message
    2, 1 "Hi admin"
    1, 3 "Hello bro"

    Check the demo here.

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

Back To Top
Search