Laravel 5.1: handle joins with same column names

Question

I'm trying to fetch following things from the database:

• user name
• user avatar_name
• user avatar_filetype
• complete conversation_messages

with the following query:

    static public function getConversation($id)
{
    $conversation = DB::table('conversation_messages')
        ->where('belongsTo', $id)
        ->join('users', 'conversation_messages.sender', '=', 'users.id')
        ->join('user_avatars', 'conversation_messages.sender', '=', 'user_avatars.id')
        ->select('users.name', 'conversation_messages.*', 'user_avatars.name', 'user_avatars.filetype')
        ->get();
    return $conversation;
}

It works fine so far, but the avatar's column name is 'name' like the column name from the 'users' table. So if I'm using this query the to get the output via $conversation->name, the avatar.name overwrites the users.name

Is there a way to rename the query output like the mysql "as" feature at laravel 5.1?

For example:

$conversation->avatarName

$conversation->userName

Answer 1

Yeah, simply rename the column on either table and it should work. Also what you can do is, rename the user.name column to anything, also rename sender column of conversation_messages to id and perform a natural join.

Answer 2

Take a look at this example of trying to join three tables staffs, customers and bookings(pivot table).

 $bookings = \DB::table('bookings')
        ->join('staffs', 'staffs.id' , '=', 'bookings.staff_id')
        ->join('customers', 'customers.id' , '=', 'bookings.customer_id')
        ->select('bookings.id', 'bookings.start_time',  'bookings.end_time', 'bookings.service', 'staffs.name as Staff-Name',  'customers.name as Customer-Name')
        ->orderBy('customers.name', 'desc')
        ->get();
    return view('booking.index')
            ->with('bookings', $bookings);

Answer 3

Meh okay.. i've found a simple solution here

->select('users.name as userName', 'conversation_messages.*', 'user_avatars.name as avatarName', 'user_avatars.filetype')

As you can mention I've added the requested "as-Feature" next to the table.columnName

Answer 4

I had the following problem, simplified example:

$result = Donation::join('user', 'user.id', '=', 'donation.user_id')->where('user.email', '[e-mail]')->first();

$result is a collection of Donation models. BUT CAREFUL:

both tables, have a 'created_at' column. Now which created_at is displayed when doing $result->created_at ? i don't know. It seems that eloquent is doing an implicit select * when doing a join, returning models Donation but with additional attributes. created_at seems random. So what I really wanted, is a return of all Donation models of the user with email [e-mail]

solution is this:

$result = Donation::select('donation.*')->join('user', 'user.id', '=', 'donation.user_id')->where('user.email', '[e-mail]')->first();