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: "swk1@gmail.com", 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.