Joining belongsToMany and getting all columns

Question

I have this method:

public function menuGroups()
{
    return $this->belongsToMany(Layout::class, '_layout_pivot', 'side_menu_item_id', 'side_menu_group_id')->withTimestamps();
}

Result is something like this (one of loop):

"id" => 1
"label" => "system/menu.settings_group"
"icon" => "icon-settings"
"sort" => 10
"system_employee_id" => 10
"created_at" => "2017-07-19 03:24:06"
"updated_at" => "2017-07-17 00:00:00"

I also have table 'system_employees' (just a few sample columns):

+----+----------------+-----------+-----------+-----+---------------------+---------------------+
| id | username       | firstname | lastname  | age | created_at          | updated_at          |
+----+----------------+-----------+-----------+-----+---------------------+---------------------+
|  1 | Raquel46932071 | Raquel    | Heller    |  25 | 2017-04-26 05:03:43 | 2017-07-03 21:13:37 |
|  2 | Brant13975229  | Brant     | Rosenbaum |  30 | 2017-04-25 21:34:46 | 2017-05-07 09:28:27 |
+----+----------------+-----------+-----------+-----+---------------------+---------------------+

system_employee_id from >belongsToMany is a foreign key of system_employees and equals system_employees.id

I am trying to combine both, to get system_employee name.

EDIT (as per aynber comment):

I join these two and use paginate(), so I cannot use get and select, or prefix columns:

$this->menuGroups
->leftJoin('system_employees', 'system_employees.id', '=', '_layout_side_menu_groups.system_employee_id')
->paginate($pages, $columns, $pageName, $page);

This is what I am getting:

"id" => 18
        "label" => "Some Insert Tester"
        "icon" => "insico"
        "sort" => 39
        "system_employee_id" => 18
        "created_at" => "2017-07-03 00:28:05"
        "updated_at" => "2017-07-03 00:28:05"
        "type" => 4
        "system_id" => 0
        "username" => "System2017"
        "firstname" => "Rita"
        "lastname" => "Veygand"
        "age" => 0
        "gender" => ""
        "email" => "[email protected]"
        "mobile" => ""
        "rank_id" => 1
        "rank_title" => ""
        "status" => 1
        "avatar" => "mysite.com/img/14161939651400nds6k.jpg"
        "password" => "$2y$10$FIL4LmjLVEZ6SgUsRILqPutTEcaGWcmKyIHwPOBbQfHDYdJM5//2."
        "remember_token" => "vUI812NTJZ8RWrlX3g3quBy4quVrue1dnOVZEPzifRJdy85cJPQY7RclYNHU"

Question:

How to get 'created_at', 'updated_at' from both tables without using Query Builder? Same applies to any other columns with same names in both tables.

In other words: is there a way to prefix columns in belongsToMany?

You'll have to specify the columns you want, and alias the duplicated columns with AS. — aynber
Possible duplicate of How to alias the name of a column in Eloquent — aynber
@aynber Thank you for pointing to that other answer. But in my question, I forgot to add, that I am using paginate(), so I cannot use get(). — Jeffz
You can use select('menu_groups.*, system_employees.id AS se_id, ...'). But if you're using Eloquent, why not just load the menu items with their relations using with()? — fubar
@fubar Thank you for your answer. I did not know, how to combine with() with belongsToMany(), which I wanted to keep using. So I found another solution. Solution, which now seems obvious. — Jeffz

Jeffz Jeffz · Accepted Answer · 2017-10-18T01:35:44

Since I am using paginate() not get() I thought I cannot specify columns.

But it can be done.

Paginate allows defining columns - it was right there looking at me.

Here is solution that worked for me:

$columns = [
            '_layout_side_menu_groups.created_at AS layout_created_at',
            '_layout_side_menu_groups.updated_at AS layout_updated_at',
            'system_employees.created_at AS system_created_at',
            'system_employees.updated_at AS system_updated_at',
];
$this->menuGroups
->leftJoin('system_employees', 'system_employees.id', '=', '_layout_side_menu_groups.system_employee_id')
->orderBy('_layout_side_menu_groups.' . $columnSorted, $sortOrder)
->paginate($pages, $columns, $pageName, $page);

All columns from all tables can be defined this way.

Joining belongsToMany and getting all columns

1 Answers