Laravel: How to search child relationship

Question

I feel this may be a simple question; but I can't, for the life of me, figure this out. I'm relatively new to Laravel, so bear with me. (And I have poured over the docs trying to find this one)

I have a simple One to Many relationship setup and everything is working as intended.

Membership hasMany Members Members belongsTo Membership

While building a search, I was searching Members with great success, but realized I needed the expiration field set in Membership while displaying results. I'm currently using a Members::Query for the search. My question is, is there a way to pull parent information from a child relationship without having to rewrite all of the search logic and (cringe) having to submit a seperate query to pull each parent record?

Here's the relavent code:

Membership Model

class Membership extends Eloquent {
    protected $table = 'memberships';
    protected $fillable = array('expires');

    public function members() {
        return $this->hasMany('Member');
    }
}

Member Model

class Member extends Eloquent {

    protected $table = 'members';
    protected $fillable = array('various','fields','here');

    public function membership() {
        return $this->belongsTo('Membership');
    }
}

Search Function

public function searchMember()
{
    $search = Input::get('search');
    $searchTerms = explode(' ', $search);
    $query = Member::query();

    $fields = array('firstname', 'middlename', 'lastname', 'email', 'dlnumber', 'membership_id');

    foreach ($searchTerms as $term)
    {
        foreach ($fields as $field)
        {
            $query->orWhere($field, 'LIKE', '%'. $term .'%');
        }
    }

    $results = $query->paginate(10);
    return View::make('members.search')->with('results', $results);
}

As mentioned earlier, everything is working as expected, I just need to be able to pull one field from the parent Membership relationship.

Thanks in advance!

EDIT: After re-reading my question, I also wanted to point out that this is the only place in the application that isn't utilizing the Eloquent ORM and eager loading. If there's a way I could/should do this utilizing those, that would be best (imo)

Answer 1

You use whereHas for this.

Member::whereHas('membership', function ($q) {
   $q->where('expiration', 'like', 'somethingToSearchFor');
})->get();

In your case like this:

note: It's getting messy with all those if, foreach and closures, so I would not leave it that way, but rather extract it appropriately. It's just as an example of how you should build your query.

$fields = array('membership' => ['expiration'], 'firstname', 'middlename', 'lastname', 'email', 'dlnumber', 'membership_id');

// orWhereHas will use joins, so we'll start with fields foreach
foreach ($fields as $relation => $field)
{
  if (is_array($field))
  {
    // here we join table for each relation
    $query->orWhereHas($relation, function ($q) use ($field, $search) {

      // here we need to use nested where like: ... WHERE key = fk AND (x LIKE y OR z LIKE y)
      $q->where(function ($q) use ($field, $search) {
        foreach ($field as $relatedField)
        {
          foreach ($search as $term)
          {
            $q->orWhere($relatedField, 'like', "%{$term}%");
          } 
        } 
      });
    });
  } 
  else
  {
    foreach ($search as $term)
    {
      $query->orWhere($field, 'like', "%{$term}%"); 
    } 
  } 
}

Above we repeated foreach ($search as $term) instead of placing it at the top, because you would end up with a join per every $term for each $relation. Like I said, refactor it and extract that code so you can use it with different relations and it doesn't look that messy.