WHERE EQUALS TO SUBQUERY SELECT

Question

i just want to know how can I convert this query into laravel eloquent query .

"SELECT *  FROM vw_part_supplier 
            LEFT JOIN purchase_request_detail ON vw_part_supplier.part_supp_id = purchase_request_detail.prd_part_supp_id
            WHERE (
            part_status = 'ACTIVE'
            AND part_supp_id not IN (SELECT prd_part_supp_id FROM purchase_request_detail WHERE prd_pr_id = $id) 
            AND part_supp_supplier_id = (SELECT pr_supplier_id FROM purchase_request WHERE pr_id = $id)
            AND  part_prod_grp_id = (SELECT pr_prod_grp_id FROM purchase_request WHERE pr_id = $id)
            AND  part_supp_ratio!=0)
            ORDER BY part_name,part_code,part_subcode";

I have done the part but only until the where not IN. Any help would surely be appreciated. Thank you!

SO is not a coding service. Please share your best attempt and the specific errors you come across. — Kurt Friars, Jul 21 '20 at 07:21
Im not saying that SO is a coding service , what I am asking is just some help because that is what I think what SO is , and my question is based on the kindness that I saw in this questions where the user did not even show any errors that he/she came across . [link](https://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in) — Kabergrammer, Jul 21 '20 at 07:31

score 0 · Answer 1 · answered Jul 21 '20 at 08:06

I believe that you tried and reached until the where not in, which is the point that gets tricky, but i also believe you could search a bit more for your answer.

Anyhow i will solve one line for you the rest just follow the same mindset:

AND part_supp_id not IN (SELECT prd_part_supp_id FROM purchase_request_detail WHERE prd_pr_id = $id)

The problem i assume you have here is the nested select, that's why it blocked you.

 ->whereNotIn('part_supp_id', function ($query) use($id) {
                $query->from("purchase_request_detail")
                      ->where("prd_pr_id", $id)
                      ->select("prd_part_supp_id");
          })

I have already solved this with my own solution , anyway thank you for your response . — Kabergrammer, Jul 21 '20 at 23:28

score 0 · Answer 2 · answered Jul 21 '20 at 23:29

I already solved this .

public function getAvailPartsList($pr_id = 0){

        $ids = $this->model
                    ->select('pr_supplier_id','pr_prod_grp_id')
                    ->from('purchase_request')
                    ->where('pr_id','=',$pr_id)
                    ->get();

        $pr_supplier_id = $ids[0]['pr_supplier_id'];
        $pr_prod_grp_id = $ids[0]['pr_prod_grp_id'];

        $result = $this->model->select('*')
                            ->from('vw_part_supplier')
                            ->leftJoin('purchase_request_detail', 'vw_part_supplier.part_supp_id','=','purchase_request_detail.prd_part_supp_id')    
                            ->where('vw_part_supplier.part_status', '=', 'ACTIVE')
                            ->whereNotIn('vw_part_supplier.part_supp_id', function ($y) use ($pr_id){
                                        $y->select('prd_part_supp_id')
                                            ->from('purchase_request_detail')
                                            ->where('prd_pr_id', '=', $pr_id);
                                    })
                            ->where('vw_part_supplier.part_supp_supplier_id', '=', $pr_supplier_id)
                            ->where('vw_part_supplier.part_prod_grp_id', '=', $pr_prod_grp_id)
                            ->where('part_supp_ratio','!=',0)
                            ->get(); 

        return $result;                 

    }

Anyways thanks for all of your responses.

WHERE EQUALS TO SUBQUERY SELECT

2 Answers2