CODEWITHSUNDEEP
phpmysqllaraveleloquentmany-to-many
Kanav
Kanav

Reputation: 2819

Filter many-to-many relationsip - Laravel

i have a dating website whose database is as follows

country (list of all countries)
-------    
id
name

users (It contains all types of users - Super Admin, Fronend user)
-----

id
role_id
name
email
password
country_id
active


user_profile(contains further details of frontend users)
------------

id
user_id
gender_id
photo
status
screenname
headline
location
sign
...and 12 more columns specific to frontend users


gender(specific to genders option - Male, Female, Gay, Prefer Not To Say, etc)
------

id
name


looking_for (pivot between user and gender)
-----------
id
user_id
gender_id

looking_for is a pivot table between user and gender. A user can date multiple genders. eg. A female having interest in dating males as well as females.

user has hasOne with user_profile

public function profileDetails(){
    return $this->hasOne('\App\UserProfile');
}

Inversly, user_profile has 'belongsTo' relation with user

public function user(){
    return $this->belongsTo('\App\User');
}

user_profile has belongsTo relation with gender because each user profile has a gender

public function gender(){
    return $this->belongsTo('\App\Gender');
}

user and gender has belongsToMany via looking_for because a user can set his dating preference to multiple genders.

// User
public function lookingFor(){
    return $this->belongsToMany('\App\Gender', 'looking_for')->withTimestamps();
}

//Gender
public function lookedUpBy(){
    return $this->belongsToMany('\App\User', 'looking_for')->withTimestamps();
}

Now, i'm trying to create a search.

eg. A female is looking to date both males and females living in US. Also have to consider that the males and females(that will be shown in the result) have set their looking_for to female.

First, I'm trying to fetch all users who have set their looking_for preference to female.

Gender::with('lookedUpBy')->find($genderIdOfSearchedUser);

How to filter it down further?

Edit

i have moved gender_id to users table as it is better suited there.

I have an interests table as well

interests (1-on-1 Dating, Online Friends, Swingers etc)
---------
id
name

and a pivot table between users and interests

interest_user
-------------
id
interest_id
user_id

So, a user can look up for multiple genders having similar interests.

Say, a female is looking to date both males and females living in US, having interest in 1-on-1 Dating and Online Friends

Upvotes: 3

Views: 1260

Answers (2)

Cptmaxon
Cptmaxon

Reputation: 525

@Gordon's answer will work, but you're basically pulling all the data from the database and iterating on the object collection nitpicking for your answer in program memory instead of leveraging your database engine, which is built for these kind of queries. I would use laravel whereHas method to query the many to many relatiobship 

//assuming you are in the User instance you are trying to search for
$user = User::first();
$userGenderPref = $user->lookingFor()->get()->lists('gender_id');
$userInterestedIn = $user->interestedIn()->get()->lists('interest_id');
$results = User::whereHas('lookingFor', function($query) use ($userPref){//looking for gender
      $query->whereIn('gender_id',$userPref);
})
->whereHas('interestedIn', function($query) use ($userInterestedIn){//interested in
      $query->whereIn('interest_id',$userInterestedIn);
})
->where('user_id','!=',$user->user_id)//this is added to filter out our current user
->where('country_id',$user->country_id) //same country code as the original user
->get();

the above code will return a list of users that have the same preferences as your user, gender and interest and it will also make sure it's in teh same country as current user.

Upvotes: 2

Gordon Freeman
Gordon Freeman

Reputation: 3421

Laravel has nice collection filters ;)

Try something like this:

$collection = Gender::with('lookingFor')->each(function($gender) {

  // get the lookingFor names
  $looking_for = $gender->lookingFor()->lists('name');

  // check if your case has been met
  if(in_array('Female', $looking_for)) return $gender;
});

Be warned, it's pseudocode ;)

Ps. If you want to learn more about Laravel Collections, here is a free ebook which i can highly recommend.

Upvotes: 0

Related Questions