I have some problems with Laravel Query. I'm trying to get some results with join, but when I pass whereNotIn, it says like:

"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select products.* from products inner join merchants on merchants.id = products.merchant_id where id not in (3) and products.deleted_at is null order by merchants.rank asc

So the code is:

$product_ids = [3];
$products = ProductHome::whereNotIn('id', $product_ids)
                ->join('merchants', 'merchants.id', '=', 'products.merchant_id')
                ->orderBy('merchants.rank')
                ->select('products.*')
                ->get();

But when I remove whereNotIn, or if it's empty, everything works fine, but it needs to be there. What am I doing wrong here?

CodePudding user response：

You need to specify the merchants table in your whereNotIn() condition, just like you are doing in the join():

$product_ids = [3];
$products = ProductHome::whereNotIn('merchants.id', $product_ids)
                ->join('merchants', 'merchants.id', '=', 'products.merchant_id')
                ->orderBy('merchants.rank')
                ->select('products.*')
                ->get();

CodePudding user response：

It happens because both the tables have an id column and SQL won't know which one to choose, Can you do something like this and check?

    $product_ids = [3];
$products = ProductHome::whereNotIn('products.id', $product_ids)
                ->join('merchants', 'merchants.id', '=', 'products.merchant_id')
                ->orderBy('merchants.rank')
                ->select('products.*')
                ->get();enter code here