Joshua Nissenbaum Joshua Nissenbaum - 6 months ago 18
SQL Question

Find results based off results using eloquent

I have the following table.

Locations

id | postcode | suburb | state


And another table:

Classifieds

id | name | location - Matches to 'id' in the first table


First I have a code which executes and saves the results of the locations from table one into a variable. Next I need to find which of the classifieds match up to the results in the variable. This is what I have so far, not really sure how to do it.

This code runs on the view:

$locations = Location::where('location', '=', $_GET['location']); (From the URL)


This code needs to find results in the 'classifieds' table that matches the classified's location id with ANY id in the $locations variable

foreach($locations as $loc)
{
foreach($classifieds as $classi)
{
if($loc->id == $classi->location)
{

}
}
}


Something like this ^, except this code doesn't work

To further explain this with an example:

Locations array variable has:

98212 | 4220 | BURLEIGH HEADS | QLD (id, postcode, suburb, state)

98213 | 4221 | BURLEIGH WATERS | QLD (id, postcode, suburb, state)

This is the result of (Classified::where('location, '=', $_GET['location')->get())

Classifieds table has:

1 | Red Bike | 98212 (id, name, location)

2 | Red Bike | 98213 (id, name, location)

3 | Red Bike | 98213 (id, name, location)


Find classifieds where the location is listed within the variable

Answer

Extract all location IDs into one array with the Collection method pluck() or lists() (depends on the version):

$locationIds = $locations->pluck(id);

and use Eloquents whereIn() method:

$classfields = Classified::whereIn('location', $locationIds)->get();

That all can be a one-liner:

$classfields = Classified::whereIn('location', $locations->pluck(id))->get();
Comments