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?
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
@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.