codecourse-databable

demo.txt

This is the example for my (ugly maybe) solution:

- My controller extends from DataTableController as usual, using the builder and getDisplayableColumns methods

class AdminUsersController extends DataTableController
{
    public function builder()
    {
        return Admin::query();
    }

    public function getDisplayableColumns()
    {
        return [
            'id', 'name', 'last', 'email', 'role'
        ];
    }
    
- In my my model Admin.php I use a scope which I created to query my two related tables

class Admin extends Model
{
    protected $table = 'users';

    /**
     * The "booting" method of the model.
     *
     * @return void
     */
    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope(new AdminScope);
    }
}

- And this is the Scope

class AdminScope implements Scope
{
    /**
     * Apply the scope to a given Eloquent query builder.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $builder
     * @param  \Illuminate\Database\Eloquent\Model  $model
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        $builder->join('role_user', 'role_user.user_id', 'users.id')
            ->join('roles', 'roles.id', 'role_user.role_id')
            ->where('role_user.role_id', '1')
            ->select('users.id as id', 'users.name as name', 'users.last as last', 'email', 'roles.display_name as role');
    }
}

- As you can see, the results have the relationships that I need.
One importante thing: if in the both tables (the relateds one) have the same fields (example: id or name) 
you must to use the notation: table.field for avoid collition.