Laravel 5.5 pivot join to get pivot values with ma

Question

I'm trying to join a pivot table on a MySQL query. Basically I'm selecting the users, where one user has multiple sub categories.

So in essence with my "sub_categories relationship, one user has many sub categories. But because I am using RAW select, I cannot select / use the relationships. Instead I have to use a join.

This is my sub_categories table

    Column  Type    Comment
    id  int(10) unsigned Auto Increment  
    main_category_id    int(10) unsigned [0]     
    category_name   varchar(100)     
    created_at  timestamp NULL   
    updated_at  timestamp NULL

and this is my pivot table

    Column  Type    Comment
    user_id int(10) unsigned     
    sub_category_id int(10) unsigned

This is my SQL query

$users= DB::table('users')
    ->select('users.*', 'user_options.*', DB::raw('
        branches.*,
        professional_profiles.tags,
        ' . $lat . '  AS latpoint,  
        ' . $lng . ' AS longpoint,
        ' . $radius . ' AS radius,
        ' . $measurement_number . ' AS distance_unit,
        (
            ' . $measurement_number . ' * DEGREES(
                ACOS(
                    COS(RADIANS(' . $lat . '))
                    * COS(RADIANS(branches.lat))
                    * COS(RADIANS(' . $lng . ' - branches.lng))
                    + SIN(RADIANS(' . $lat . '))
                    * SIN(RADIANS(branches.lat))
                )
            )
        ) AS distance
        '), 'users.id AS id')
        ->leftJoin('branches', 'users.branch_id', '=', 'branches.id')
        ->leftJoin('user_options', 'user_options.user_id', '=', 'users.id')
        ->leftJoin('professional_profiles', 'professional_profiles.user_id', '=', 'users.id')
        ->where('user_options.professional', '>', 0)
        ->where('users.branch_id', '>', 0)
        ->where(function ($x) use ($term) {
            $x->where('branches.branch_name', 'like', '%' . $term . '%')
                ->orWhere('branches.branch_city', 'like', '%' . $term . '%')
                ->orWhere('users.firstname', 'like', '%' . $term . '%')
                ->orWhere('users.lastname', 'like', '%' . $term . '%')
                ->orWhere('professional_profiles.tags', 'like', '%' . $term . '%');
        })
        ->having('distance', 'orderBy('distance', 'asc')
        ->limit(50)
        ->get();

And this is my result:

    [
        {
            id: 4,
            profile_id: 2,
            branch_id: 3,
            prefix: "dr",
            firstname: "SWK1",
            lastname: "Doe",
            email: "[e-mail]",
            mobile_no: "811692244",
            password: "$2y$10$LzkPwc2TZu/.UzB.0mYJ",
            avatar: "123.jpg",
            remember_token: "wF33ShLirtvS3mIYJpmg5skVVoohGJCS7v",
            created_at: "2017-10-12 09:32:05",
            updated_at: "2017-10-12 09:32:05",
            provider: null,
            provider_id: null,
            user_id: 4,
            profile_administrator: 0,
            branch_administrator: 0,
            professional: 1,
            branch_name: "Swakopmund 1",
            branch_address_1: "14 Backer St",
            branch_address_2: null,
            branch_city: "Swakopmund",
            branch_state: null,
            branch_zip: "9000",
            branch_country: "NA",
            branch_phone: "77777",
            main_image: null,
            lat: -22.67,
            lng: 14.53,
            description: "Swakopmund 1",
            tags: "Doctors,Dietician,General Practitioner",
            latpoint: "-22.5608807",
            longpoint: "17.0657549",
            radius: 500,
            distance_unit: "111.045",
            distance: 260.210154298872
        }
    ]

So essentially the question would be to join the sub_categories table on the users table, by making use of the values set by the pivot table, without relying on the eloquent relationships table but rather by making use of a SQL.

Since one user has many sub_categories, it would be great to return the sub_categories as an array value joined on the main SQL query.

Answer 1

I had similar situation and I Query Scope along with my pivot table for one to many relation. In my situation, User has multiple groups and I need to fetch those data along with user object without extra query or without JOINs. See Query scope and one to many and many to many with pivot on Laravel Doc.

If you want to fetch data using pivote table, here is the example
User Model:

class User extends Authenticatable
{
    use Notifiable;


    protected $fillable = [
        'name', 'email', 'username', 'password',
    ];


    protected $hidden = [
        'password', 'remember_token',
    ];


    public function groups()
    {
        return $this->belongsToMany('App\Group', 'user_groups', 
          'user_id', 'group_id');
    }
    public function scopeDetail($query)
    {
        return $query->with('groups');
    }
}

Group Model:

class Group extends Model
{
    protected $fillable = [
        'dn', 'cn', 'description',
    ];
}

In above user model, see return $this->belongsToMany('App\Group','user_groups', 'user_id', 'group_id');, where user_groups is my pivot table which defines the relationship between users and group. group_id and user_id are the fields in pivote table.

Now Fetching data (on controller) using above architechture:

User::where(.....)->detail()->first();

where detail() is my scope defined in User model as scopeDetail. Note: scope prefix must be attached. This will give you the user with all the groups that user belongs to in array, so whenever you view your data in JSON you can see the structure in proper way.

Using above method, my user object has all the groups that user belongs to.

Extra
If your user model(users) related to other models too then you can include all those by defining scope on model class as

............
//..............
    public function profile()
    {
        return $this->belongsToMany('App\Profile', 'user_id');
    }
    public function data1()
    {
        return $this->belongsToMany('App\Data1', 'user_id');
    }
    public function groups()
    {
        return $this->belongsToMany('App\Group', 'user_groups', 
          'user_id', 'group_id');
    }
    //Defining query scope................
    public function scopeDetail($query)
    {
        return $query->with('groups','profile','data1');
        //to fetch user with this scope use User::where(.....)->detail()->get(); notice there is not scope prefix while using the scope
    }
........
........