CODEWITHSUNDEEP
phplaravelsortingeloquent
Roman
Roman

Reputation: 3941

Laravel eloquent merge two results and sort

I have found tons of posts about this issue and yet no solution work.

Maybe its because of my laravel version. Its 5.7.

I have two results, which look like this:

result_a = DB::connection('mysql_live')->table('user_chatmessages')
->where('from_user', $data->chat_from_user)->get();

$result_b = DB::connection('mysql_live')->table('user_chatmessages')
->where('to_user', $data->chat_from_user)->get();

Now I merge them, which works great:

$merged = $result_a->merge($result_b);
$result = $merged->values()->sortByDesc('date_added');

The sorting does not affect the whole result. The problem is that the sorting sorts first result_a and then result_b.

I tried tons of different syntax variants:

$result = $result_a->merge($result_b)->sortBy('date_added');

$merged = $result_a->merge($result_b);
$result = $merged->sortByDesc('date_added');

$merged = $result_a->merge($result_b)->sortByDesc('date_added');
$result = $merged->all();

$result = ($result_a->merge($result_b))->sortBy('date_added');

Probably even more, I am sitting for quite some time on this issue already and all I find are threads where it looks super simple and people tell it works.

I also tried to sort by ID with the same results.

EDIT

The alternative solution provided by @N69S works for the case if you want to get all received and sent messages of a certain user.

But how do you get the messages between two specific users using the same approach? For example I want to get the chatmessages between the user with ID 1 and the user with ID 2 this will not work:

$result = DB::connection('mysql_live')
                    ->table('user_chatmessages')
                    ->where('from_user', $data->chat_from_user)
                    ->where('to_user', $data->chat_to_user)
                    ->orWhere('from_user', $data->chat_to_user)
                    ->orWhere('to_user', $data->chat_from_user)
                    ->orderBy('date_added', 'desc')
                    ->get();

What I am trying right now looks like this:

$result = DB::connection('mysql_live')->table('user_chatmessages')
    ->where(function ($query) use ($from, $to) {
        $query->where('from_user', $from)->where('to_user', $to);
    })->orWhere(function ($query) {
        $query->where('from_user', $to)->where('to_user', $from);
    })->orderBy('date_added', 'asc')->get();

But I get an error: from & to are not defined.

This works perfectly:

$result = DB::connection('mysql_live')->table('user_chatmessages')
        ->where(function ($query) {
            $query->where('from_user', '1')->where('to_user', '2');
        })->orWhere(function ($query) {
            $query->where('from_user', '2')->where('to_user', '1');
        })->orderBy('date_added', 'asc')->get();

Upvotes: 0

Views: 792

Answers (3)

N69S
N69S

Reputation: 17206

Why dont you recover all the results in one query ?

result_a = DB::connection('mysql_live')
    ->table('user_chatmessages')
    ->where('from_user', $data->chat_from_user)
    ->orWhere('to_user', $data->chat_from_user)
    ->orderBy('date_added', 'desc')
    ->get();

or user ->orderBy('created_at', 'desc') if you have the default timestamps fields

EDIT

For recovering the chat between two specific users, you need to make use of the parenthesis.

result_a = DB::connection('mysql_live')
    ->table('user_chatmessages')
    ->where(function($query) use($data) {
        $query->where('from_user', $data->chat_from_user)
            ->where('to_user', $data->chat_to_user);
    })
    ->orWhere(function($query) use($data) {
        $query->where('to_user', $data->chat_from_user)
            ->where('from_user', $data->chat_to_user);
    })
    ->orderBy('date_added', 'desc')
    ->get();

Upvotes: 2

Zohaib
Zohaib

Reputation: 662

If you wan to use two queries then you can use union.

Sub query 

$subQuery = DB::connection('mysql_live')
->table('user_chatmessages')
->where('from_user', $data->chat_from_user);

Add sub query by using union on main query

$results = DB::connection('mysql_live')
->table('user_chatmessages')
->where('to_user', $data->chat_from_user)
->union($subQuery)                         //see
->orderBy('date_added', 'desc')            //see
->get();

This should work. You can use what ever queries just make union of them to avoid merge in collections and then sort by date_added

Upvotes: 0

Ahmed Atoui
Ahmed Atoui

Reputation: 1556

you can get the data directly without using merging like this:

result = DB::connection('mysql_live')
         ->table('user_chatmessages')
         ->where(function($query) use ($data){
           $query->where('from_user', $data->chat_from_user)->orWhere('to_user', $data->chat_from_user)
         })->orderBy('date_added','desc')
           ->get();

Upvotes: 0

Related Questions