CODEWITHSUNDEEP
mysqllaravel
Vinícius
Vinícius

Reputation: 483

How to do this in Laravel, subquery where not in

I need to transform this query in Laravel Format, I try use the 'WhereNotIn' , but I dont' have the result what I want. Somebody can help me? 

SELECT
em.erp_mlbid AS category_id

FROM
erp_product AS ep
INNER JOIN
erp_product_description AS epd ON epd.erp_productid = ep.erp_productid
INNER JOIN
erp_product_category AS epc ON epc.erp_productid = ep.erp_productid
INNER JOIN
erp_mlbcategory_erpcategory AS emc ON emc.erp_categoryid = epc.erp_categoryid
INNER JOIN
erp_mlb_category AS em ON em.erp_mcid = emc.erp_mlbcategoryid
INNER JOIN
erp_product_image AS epi ON epi.erp_productid = ep.erp_productid
WHERE
ep.erp_productid NOT IN (
SELECT
epm.erp_productid
FROM
erp_product_to_mlb AS epm
)
AND ep.erp_quantity > 0
AND ep.erp_status > 0
LIMIT
                    1

Apparently my line 'WhereNotIn' is ignored and the result is the same:

 $categoria = DB::table('erp_product')
                            ->join('erp_product_category','erp_product_category.erp_productid', '=', 'erp_product.erp_productid')
                            ->join('erp_mlbcategory_erpcategory', 'erp_mlbcategory_erpcategory.erp_categoryid', '=','erp_product_category.erp_categoryid')
                            ->join('erp_mlb_category', 'erp_mlb_category.erp_mcid', '=', 'erp_mlbcategory_erpcategory.erp_mlbcategoryid')
                            ->select('erp_mlb_category.erp_mlbid')
                            ->whereNotIn('erp_product.erp_productid', function($q){
                                $q->select('erp_productid')->from('erp_product_to_mlb');
                            })
                            ->get();

Upvotes: 0

Views: 1007

Answers (1)

Jeremy Harris
Jeremy Harris

Reputation: 24549

You have a typo:

->whereNotIn('erp_product.erp_producid'

Should be:

->whereNotIn('erp_product.erp_productid'

Notice the misspelled productid.

One thing you can do to debug is to remove the ->get() call so that $categoria is a query object, and then do this so you can see the assembled query:

dd($categoria->toSql());

Upvotes: 3

Related Questions