Ive got three tables: products, criterias, users. Criterias table contains data of product specification criterias: id, product_id, gender, age_from, age_to.
Users(id, username, birthday, gender) and products(id, name, description) tables contains regular data. The main problem is that I cant find a way to make query which will take only these products which has a match of criterias and users. For example product has criterias: Male, age from 22 to 50. So if user with gender male and age between 22 and 50 exists, take product. Here is what I've tried:
$products = Product::whereHas('criterias', function($query) {
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('users');
$query->where(function($query){
$query->whereRaw('users.gender = criterias.gender');
$query->orWhereNull('criterias.gender');
return $query;
});
$query->where(function($query){
$query->whereRaw('criterias.age_from < TIMESTAMPDIFF(YEAR, users.birth_date, CURDATE())');
$query->orWhereNull('criterias.age_from');
return $query;
});
$query->where(function($query){
$query->whereRaw('criterias.age_to > TIMESTAMPDIFF(YEAR, users.birth_date, CURDATE())');
$query->orWhereNull('criterias.age_to');
return $query;
});
})
return $query;
})->paginate(5);
But this didn't worked.
via Sidas