CODEWITHSUNDEEP
mysqllaraveleloquent
user3201500
user3201500

Reputation: 1618

Convert raw query to Eloquent query

i am trying to get the same result which i have already received using raw query in eloquent. Here is my table looks like: 

id  sent_by message         read    received_by created_at  updated_at  

1       7   Hello Mam hello 1           1       2018-04-03 16:53:56 2018-04-03 18:20:47 
2       6   Hello Mam hello 1           1       2018-04-03 16:54:12 2018-04-03 18:20:35 
3       7   Hello Mam hello 1           1       2018-04-03 16:54:21 2018-04-03 18:20:47 
4       7   Hello Mam hello 1           1       2018-04-03 16:55:34 2018-04-03 18:20:47 
7       6   Hello Mam hello 1           1       2018-04-03 17:34:02 2018-04-03 18:20:35 
8       6   Hello Mam hello 0           1       2018-04-03 19:21:03 2018-04-03 19:21:03

I have this raw query which already works for me:

SELECT * FROM `en_chat_message` 
WHERE id IN (SELECT MAX(id) AS id
             FROM `en_chat_message`  
             WHERE received_by=1 OR sent_by=1
             GROUP BY sent_by) 
ORDER BY created_at DESC

From which here is my output looks like:

id sent_by message read received_by created_at updated_at 

8       6   Hello Mam hello     0           1       2018-04-03 19:21:03 2018-04-03 19:21:03 
4       7   Hello Mam hello     1           1       2018-04-03 16:55:34 2018-04-03 18:20:47

Now the only thing is i want to convert this query to eloquent query. I am not getting idea related to executing the model name.

Thank you! (in advance)

Upvotes: 0

Views: 107

Answers (2)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Using the query builder:

DB::table('en_chat_message')
    ->whereIn('id', function($query) {
        $query->selectRaw('MAX(id) id')
            ->from('en_chat_message')
            ->where('received_by', 1)
            ->orWhere('sent_by', 1)
            ->groupBy('sent_by');
    })
    ->orderByDesc('created_at')
    ->get();

Using an Eloquent model:

class EnChatMessage extends \Illuminate\Database\Eloquent\Model
{
    protected $table = 'en_chat_message';
}

EnChatMessage::whereIn('id', function($query) {
    $query->selectRaw('MAX(id) id')
        ->from('en_chat_message')
        ->where('received_by', 1)
        ->orWhere('sent_by', 1)
        ->groupBy('sent_by');
    })
    ->orderByDesc('created_at')
    ->get();

Upvotes: 1

BM2ilabs
BM2ilabs

Reputation: 532

Chat::select(DB::raw('SELECT MAX(id) AS id, sent_by, message, read, received_by, created_at, updated_at'))
                ->where('received_by', 1)
                ->orWhere('sent_by', 1)
                ->groupBy('sent_by')
                ->orderBy('created_at', 'desc')
                ->get();

Upvotes: 0

Related Questions