CODEWITHSUNDEEP
mysqllaravellaravel-5
Rajasekar D
Rajasekar D

Reputation: 560

Laravel: Combining the results with UnionAll and where conditions

Lets just say I have 2 tables

resume_profiles Table:

   user_id  current_location      city
   | 3 |     | Chennai  |    | Kolkatta   |
   | 4 |     | Mumbai   |    | Ahmaedabad |
   | 5 |     | Pune     |    | Kolkatta   |
   | 6 |     | Kolkatta |    | Pune       |

resume_locations Table:

   user_id     location
   | 2 |     | Chennai  |
   | 2 |     | Mumbai   |
   | 3 |     | Pune     |
   | 4 |     | Kolkatta |

I need to combine these result table locations into a single SET where resume_profiles.user_id = resume_locations.user_id using Union ALL, So I'd have something like this:

   City        Aggregate
| Chennai    |    | 1 |
| Mumbai     |    | 1 |
| Pune       |    | 2 |
| Kolkatta   |    | 3 |
| Ahmaedabad |    | 1 |

Working Query without where user_id:

$preferred_locations = ResumeLocation::selectRaw('location as city');

$current_locations = ResumeProfile::selectRaw('current_location as city');

$subquery = ResumeProfile::selectRaw('city')
            ->unionAll($current_locations)
            ->unionAll($preferred_locations);

$locations = DB::table(DB::raw("({$subquery->toSql()}) AS s"))
            ->selectRaw('s.city,count(*) as aggregate')
            ->groupBy('s.city')
            ->get();

** Getting Exception in Query while using were condition :**

$preferred_locations = ResumeLocation::selectRaw('location as city')
            ->where('resume_locations.user_id','resume_profiles.user_id');

$current_locations = ResumeProfile::selectRaw('current_location as city');

$subquery = ResumeProfile::selectRaw('city')
            ->unionAll($current_locations)
            ->unionAll($preferred_locations);

$locations = DB::table(DB::raw("({$subquery->toSql()}) AS s"))
            ->selectRaw('s.city,count(*) as aggregate')
            ->groupBy('s.city')
            ->get();

I don't know how to achieve this where condition in a table.

Upvotes: 0

Views: 509

Answers (1)

MohamedSabil83
MohamedSabil83

Reputation: 1559

whereIn:

$preferred_locations = ResumeLocation::selectRaw('location as city')
->whereIn('user_id', function($q) {
  $q->select('user_id')->from('resume_profiles');
});

Upvotes: 1

Related Questions