Fetch products by Supplier

mail@pastecode.io avatar
unknown
plain_text
2 months ago
4.2 kB
2
Indexable
Never
SELECT jsonb_build_object('title', "pro"."title",'description', "pro"."description",'watermelon_product_code', "pro"."handle",'hs_code', "pro"."hs_code",'type', "pro"."product_type",'product_code', "pro"."product_code",'is_deactivated', "pro"."is_deactivated",'avaliabilty_status', "pro"."avaliabilty_status",'status', "pro"."status",'is_vat_included', "pro"."is_vat_included",'supplier_id', "pro"."supplier_id",'metadata', "pro"."metadata",'variants', pro_vars.variants,'tags', pro_tags.tags,'category', cat.*,'sub_category', sub_cat.*,'sub_sub_category', sub_sub_cat.*,'brand', brand.*,'country', country.*,'customer', prod_customer.customer,'supplier', pro_sup.supplier,'ware_house', sup_wah.ware_house,'images', pro_images.images,'master_product_id', "pro"."master_product_id",'id', "pro"."id" )as product FROM "public"."product" "pro" LEFT JOIN (SELECT "pv"."product_id" as product_id, jsonb_agg(jsonb_build_object('title', "pv"."title",'metadata', "pv"."metadata",'inventory_quantity', "pv"."inventory_quantity",'is_vat_included', pv.is_vat_included,'currency_code', "amt"."currency_code",'min_quantity', "amt"."min_quantity",'unit_price', "amt"."amount",'id', "pv"."id" ))as variants FROM "public"."product_variant" "pv" LEFT JOIN "public"."money_amount" "amt" ON  "amt"."variant_id" = "pv"."id" AND "amt"."deleted_at" IS NULL WHERE "pv"."deleted_at" IS NULL GROUP BY "pv"."product_id") "pro_vars" ON pro_vars.product_id = "pro"."id"  LEFT JOIN (SELECT "pts"."product_id" as product_id, jsonb_agg(jsonb_build_object('value', "pt"."value",'id', "pt"."id" ))as tags FROM "public"."product_tags" "pts" LEFT JOIN "public"."product_tag" "pt" ON  "pt"."id" = "pts"."product_tag_id" AND "pt"."deleted_at" IS NULL GROUP BY "pts"."product_id") "pro_tags" ON pro_tags.product_id = "pro"."id"  LEFT JOIN "public"."product_category_product" "pro_cat_pro" ON "pro_cat_pro"."product_id" = "pro"."id"  LEFT JOIN "public"."product_category" "cat" ON "cat"."id" = "pro_cat_pro"."product_category_id"  LEFT JOIN "public"."product_sub_category" "sub_cat" ON "sub_cat"."id" = "pro"."subtitle"  LEFT JOIN "public"."product_sub_sub_category" "sub_sub_cat" ON "sub_sub_cat"."id" = "pro"."mid_code"  LEFT JOIN "public"."product_brand" "brand" ON "brand"."id" = "pro"."material"  LEFT JOIN "public"."country" "country" ON "country"."iso_2" = "pro"."country_id"  LEFT JOIN (SELECT "pro_img"."product_id" as product_id, jsonb_agg(jsonb_build_object('url', "img"."url",'metadata', "img"."metadata",'id', "img"."id" ))as images FROM "public"."product_images" "pro_img" INNER JOIN "public"."image" "img" ON  "img"."id" = "pro_img"."image_id" AND "img"."deleted_at" IS NULL GROUP BY "pro_img"."product_id") "pro_images" ON pro_images.product_id = "pro"."id"  LEFT JOIN (SELECT "sup"."id" as supplier_id,jsonb_build_object('email_id', "sup"."email_id",'business_name', "sup"."business_name",'contract_file', "sup"."contract_file",'status', "sup"."status",'id', "sup"."id" ) as supplier FROM "public"."business_setup" "sup" GROUP BY "sup"."id") "pro_sup" ON pro_sup.supplier_id = "pro"."supplier_id"  LEFT JOIN (SELECT "wh"."business_setup_id" as supplier_id,jsonb_build_object('contact_name', "wh"."contact_name",'phone_number', "wh"."phone_number",'id', "wh"."id" ) as ware_house FROM "public"."business_store" "wh" WHERE "wh"."deleted_at" IS NULL GROUP BY "wh"."business_setup_id", "wh"."contact_name", "wh"."id", "wh"."phone_number") "sup_wah" ON sup_wah.supplier_id = "pro"."supplier_id"  LEFT JOIN (SELECT "bs"."id" as supplier_id,jsonb_build_object('id', "cus"."id",'first_name', "cus"."first_name",'last_name', "cus"."last_name",'email', "cus"."email",'is_offline_supplier_activated', cus.is_offline_supplier_activated,'buyer_id', cus.buyer_id ) as customer FROM "public"."customer" "cus" INNER JOIN "public"."business_setup" "bs" ON "bs"."customer_id" = "cus"."id"  INNER JOIN "public"."product" "pro" ON  "pro"."supplier_id" = "bs"."id" AND "pro"."deleted_at" IS NULL WHERE "cus"."deleted_at" IS NULL GROUP BY "cus"."id", "bs"."id") "prod_customer" ON prod_customer.supplier_id = "pro"."supplier_id" 
WHERE ( "pro"."supplier_id" = 'bus_01HP1P6HB92H5TWBKQ5VJFNEPJ' AND "pro"."product_type" = ANY('online') ) AND ( "pro"."deleted_at" IS NULL )
Leave a Comment