SQL to Query Builder

I have an sql statement that I am struggling to convert to a Query Builder statement:

SELECT m1.*
FROM chat m1 LEFT JOIN chat m2
 ON (m1.chat_id = m2.chat_id  AND m1.id < m2.id)
WHERE (m1.from_id = 4 or m1.to_id=4) 
AND m2.id IS NULL

Any help is appreciated!

tykus

4 years ago

Level 104

@theor like this:

DB::table('chat as m1')->leftJoin('chat as m2', function (JoinClause $join) {
   $join->on('m1.chat_id', '=', 'm2.chat_id')->where('m1.id', '<', 'm2.id');
})->where(function ($builder) {
   $builder->('m1.from_id', 4)->orWhere('m1.to_id', 4);
})->whereNull('m2.id')->get()

TheoR

4 years ago

Level 14

@tykus Thanks. I gave that a try, but I get back several "null" records.

TheoR

4 years ago

Level 14

Here is my original query, which works, but I'm trying to get back the last record from each group, not the first:

             $this->Chats = Chat::select(DB::raw('*, max(created_at)'))
                    ->where('to_id', Auth::user()->id)
                    ->orWhere('from_id', Auth::user()->id)
                    ->orderBy('created_at', 'desc')
                    ->groupBy('chat_id')
                    ->get();

tykus

4 years ago

Level 104

@TheoR you say above

I have an sql statement that I am struggling to convert to a Query Builder statement:

That is not the raw SQL that would result from:

Chat::select(DB::raw('*, max(created_at)'))
                    ->where('to_id', Auth::user()->id)
                    ->orWhere('from_id', Auth::user()->id)
                    ->orderBy('created_at', 'desc')
                    ->groupBy('chat_id')
                    ->get();

Which query are you actually wanting here?

TheoR

4 years ago

Level 14

@tykus Well, I want to get back my "chats" table grouped by the chat_id field, but I want to have the latest record, not the first. However is best to get that result is what I'd like. Preferably back to my "Chat" model.

tykus

4 years ago

Level 104

@TheoR this should do it

Chat::where('to_id', Auth::user()->id)
    ->orWhere('from_id', Auth::user()->id)
    ->orderByRaw('max(created_at) desc')
    ->groupBy('chat_id')
    ->get();

TheoR

4 years ago

Best Answer

Level 14

@tykus that is so close! It does give me back the right records, but they still are showing the first record, not the latest. I even tried to switch the 'desc' to 'asc' (grasping at straws), but no dice.

TheoR

4 years ago

Level 14

So, this works:

        $this->Chats = DB::table('chat as t')
            ->select('t.*')
            ->leftJoin('chat as t1', function ($join) {
                $join
                    ->on('t.chat_id', '=', 't1.chat_id')
                    ->whereRaw(DB::raw('t.created_at < t1.created_at'));
            })
            ->where(function ($query) {
                $query
                    ->where('t.from_id', Auth::user()->id)
                    ->orWhere('t.to_id', Auth::user()->id);
            })
            ->whereNull('t1.id')
            ->get();

But, now my issue is that I really do need it in the Chat model. Is there a way to take those results and move them to a model? I tried

$chatModel = Chat::merge($Chats);

But doesn't work.

TheoR

4 years ago

Level 14

Ok, so this works, probably not the best way. but it'll at least work for now until I find a better solution:

        $this->Chats = Chat::where('to_id', Auth::user()->id)
            ->orWhere('from_id', Auth::user()->id)
            ->orderBy('created_at', 'desc')
            ->get();

        $this->Chats = $this->Chats->unique(function ($item) {
            return $item['chat_id'];
        });

Please or to participate in this conversation.