laravel whereHas results weird

user_259752 0
Viewed 2015

product model

    public function country() {
        return $this->hasMany('App\Models\ProductCountry', 'product_id', 'id');
    }

the controller

$product = Product::where('mall_' . $this->mall_id, '=', 1)
    ->whereHas('country', function ($i) use ($me) {
        return $i->where('country_id', $me->country_id);
    })
    ->find($key);

the raw SQL:

select * from `product` where `mall_3` = '1' and exists (select * from `product_country` where `product_country`.`product_id` = `product`.`id` and `country_id` = '109') and `product`.`id` = '3' and `product`.`deleted_at` is null limit 1

above SQL return no results(when the product id = 3

below SQL return the correct result(when the product id = 6)

select * from `product` where `mall_3` = '1' and exists (select * from `product_country` where `product_country`.`product_id` = `product`.`id` and `country_id` = '109') and `product`.`id` = '6' and `product`.`deleted_at` is null limit 1

have no idea, the query look like no problem

previous project i got this issues too, at the end i use find and loop once more instead of using whereHas, but this time i encounter this problem again and try to find out why but few hours wasted, still no clue, below is a workaround(ignoring the bugs?)

$products = array();
foreach ($request->get('quantity') as $key => $var) {
    if ($var > 0) {
        $product = Product::with(['country'])->where('mall_' . $this->mall_id, '=', 1)
                    ->find($key);

        foreach ($product->country as $c) {
            if ($c->country_id == $me->country_id && !isset($products[$product->id])) {
                //Do something here...
            }
        }
    }
}

enter image description here

enter image description here

enter image description here

enter image description here

laravel-5 eloquent laravel mysql php
6 Answers

i created the tables mentioned above (giving exactly same relations, table names, model names) and tried your code (by giving hard coded values) 

public function try1(){
    $me = 109;
    $key = 3;
    $product = Product::where('mall_3' , '=', 1)
        ->whereHas('country', function ($i) use ($me) {
            return $i->where('country_id', $me);
        })

        ->find($key);

    return $product;
}

public function try2(){
    $me = 109;
    $key = 6;
    $product = Product::where('mall_3' , '=', 1)
        ->whereHas('country', function ($i) use ($me) {
            return $i->where('country_id', $me);
        })

        ->find($key);

    return $product;
}

but got the perfect results

 {"id":3,"mall_1":1,"mall_2":1,"mall_3":1}

and

{"id":6,"mall_1":1,"mall_2":1,"mall_3":1}

pls check if everything is fine with your database, check if every primary key and foreign keys are created as "integer". And check "collation" of db well as tables.

user_48543090

This doesn't seem Laravel-related (since you still get no results when running the query directly with MyAdmin).

select * from `product`
where true
-- and `mall_3` = '1'
and exists (
    select * from `product_country`
    where true
    and `product_country`.`product_id` = `product`.`id`
    and `country_id` = '109'
)
and `product`.`id` = '3'
and `product`.`deleted_at` is null
limit 1

Run the query again, commenting each where condition one by one (I added a true first condition to easily comment out all "and ..." lines) to see if you spot where the problem begins.

(I did try creating both tables, and both queries return correct results, by the way.)

user_32082580

First things first:

If you're using eloquent (find method), you can do something like:

Product::find($id);

and it returns a model of product where the id you've provided equals the primary key. Having that said, there is no need to use a whereHas() since it will have no effect. Instead of find(), you can try get() or first() for example, depending on what you're looking for.

If you want to print out your query, you can end it with ->toSql(); and it will print the query as a string (helps a lot!).

I didn't paid much attention to the objective but I'm sure if you use the 

dd(Product::where('mall_' . $this->mall_id, '=', 1)
->whereHas('country', function ($i) use ($me) {
    return $i->where('country_id', $me->country_id);
})
->toSql());

You'll be able to compare what you're doing (since you know what the correct result or query should be like) and be able to figure it out.

After all, we do need to exercise to explore our mind!

user_25153500

try this hope it will help you,

 $products = array();
    foreach ($request->get('quantity') as $key => $var) {
        if ($var > 0) {
            $product = Product::where('mall_' . $this->mall_id, '=', 1)
                        ->leftjoin('product_country','product_country.product_id','=','product.id')->get();
            $products[$product->id] = ['product_id'=>$product->id,'country_id'=>$product->country_id];              
        }
    }
user_45760460

I think you should change your relationship concept in the product model from hasMany to belongsToMany.
Also update the parameters by defining the country model as first argument, intermediate table name as second argument then foreign key and primary key as 3rd and 4th arguments. belongsToMany('App\Models\Country', 'product_country','product_id', 'country_id')

Product Model

public function country() {
    return $this->belongsToMany('App\Models\Country', 'product_country','product_id', 'country_id');
}

Because each country may belong to many products and each product may belong to many countries.

user_56526720

I know this thread is a little bit stale, but my team actually had a very similar issue with Laravel and the whereHas (MySQL EXISTS) on MySQL 5.7.18.

As soon as we upgraded MySQL, the same query started to return exactly as expected. I haven't yet figured out if this was a bug with 5.7.18, but I suspect it might be.

user_92044230
Related