title: Laravel 5.5 pivot join to get pivot values with main MySQL result
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>This is my sub_categories table</p>
  
  <pre>
  
      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
  
  
  </pre>
  
  <p>and this is my pivot table</p>
  
  <pre>
  
      Column  Type    Comment
      user_id int(10) unsigned     
      sub_category_id int(10) unsigned
  
  
  </pre>
  
  <p>This is my SQL query</p>
  
  <pre>
  
  $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();
  
  </pre>
  
  <p>And this is my result:</p>
  
  <pre>
  
      [
          {
              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
          }
      ]
  
  </pre>
  
  <p>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.</p>
  
  <p>Since <strong>one user has many sub_categories,</strong> it would be great to return the sub_categories as an array value joined on the main SQL query.</p>