Monday, May 22, 2017

Taking data only if exists in other database

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

Advertisement