laravel whereHas results weird

Question

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...
            }
        }
    }
}

Answer 1

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.

Answer 2

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.)