CODEWITHSUNDEEP
phpmysqllaraveljoineloquent
Christopher
Christopher

Reputation: 3469

Laravel/Eloquent/DB Query - Join when is null and not null

I'm trying this code without any success:

MyModel::join('countries', 'countries.id', '=', 'messages.from_country_id')
         ->join('users', 'users.id', '=', 'messages.user_id')
         ->where('messages.id', '=', $id)
         ->select('users.name', 'messages.from_name', 'messages.from_email', 'messages.content', 'countries.code AS c_code', 'countries.name AS c_name')->firstOrFail();

But in some cases messages.user_id is null. What i want is to retrieve all messages, and each message with the correct users.name(when it's available).

Upvotes: 1

Views: 7681

Answers (1)

STLMikey
STLMikey

Reputation: 1210

I think you're asking for a left join, which will get all messages, as well as the username for those that have it:

MyModel::join('countries', 'countries.id', '=', 'messages.from_country_id')
     ->leftJoin('users', 'users.id', '=', 'messages.user_id')
     ->where('messages.id', '=', $id)
     ->select('users.name', 'messages.from_name', 'messages.from_email', 'messages.content', 'countries.code AS c_code', 'countries.name AS c_name')->firstOrFail()

Upvotes: 5

Related Questions