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

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 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

I had the following problem, simplified example:

$result = Donation::join('user', 'user.id', '=', 'donation.user_id')->where('user.email', '[email protected]')->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 [email protected]

solution is this:

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