Eloquent/Query Builder for more complicated table structures

Hi guys,

OK so this probably isn't the biggest problem in the world and I'm sure someone will be able to help me out with where I'm going wrong. Here's a few facts before I get to my problem:

Laravel 5 & mysql
I have 3 main tables: meetups, events and locations
I also one pivot table: event_location
Meetups hasMany Events
Events belongsTo Meetups
Events belongsToMany Locations
Locations belongsToMany Events

The problem

I'm currently trying to search the locations (via a form – i.e. `Input::get('data), and this would bring up all events that have that location, and all meetups that associate with that event.

Within my SearchController, I have the following:

// Get the searched data
$data = Input::get('data');

// Get all location, which are 'LIKE' the inputted data.
$locations = Location::where('title', 'LIKE', '%'. $data .'%')->get();

At this point I'm not sure how to proceed. I need to access the events from each location and after that I'd need a get the meetup that corresponds with that location, but I'm not sure how to go about this. Can this be done with joins() or would I loop through the $locations variable and get the ->events for each of those and put them into an array? Or, should I be using DB:: and running a custom query?

Hope someone can help me, thanks in advanced!

Luke

11 years ago

Level 3

Whenever I have a more complex query like that, I just write it in SQL and run it in MySQL workbench to see what I want and what the result looks like. After that, I can deduct the code from the actual SQL query. If I use the query builder or an Eloquent model depends a bit on the situation.

So, the question to you is: How would your raw working SQL look like?

1 like

pmall

11 years ago

Level 56

You can query relations.

All meetups which have events which have a particular location :

$query = Meetup::whereHas('events.locations', function($q) use($location)
{
  $q->where('title', 'LIKE', '℅' . $location . '%');
});

Then eagerload only the desired events :

$query->with('events.locations', function($q) use($location)
{
  $q->where('title', 'LIKE', '℅' . $location . '%');
});

Then get the meetups :

$meetups = $query->get();

Of course you can put the callback in a variable to write it once. And you can achieve this with join. Wherehas uses subqueries.

(Nested wherehas works with L5, with L4 you have to nest two wherehas)

1 like

JarekTkaczyk

11 years ago

Level 53

@lukewh @pmall gave you pretty much all. Here's the example of what you asked for considering that you can pass single location or multiple locations to look for:

$eventsQuery = Event::with('meetup');

$locations = Input::get('location');

if ($locations && is_string($locations)) // don't apply redundant subqueries and don't pass eg. an array
{
   $eventsQuery->whereHas('locations', function ($q) use ($locations) {
      $q->where('title', 'like', "%{$locations}%");
   });
}
else if (is_array($locations) && count($locations)) // if you want to pass multiple locations as an array
{
   $eventsQuery->whereHas('locations', function ($q) use ($locations) {
      $q->where(function ($q) use ($locations) { // wrap or where clauses in (..)
        foreach ($locations as $location)
        {
          $q->orWhere('title', 'like', "%{$location}%");
        }
      }
   });
}

$events = $eventsQuery->get();

1 like

Please or to participate in this conversation.