Having clause is not working with union query.

[atapatel]

• Laravel Version: 8.83.12
• PHP Version: 7.4.27
• Database Driver & Version: Mysql 8.0

Description:

I try to run complex union query using DB Facade. but result generated are without having clause effect. i can share you my query here.

public function getAutoReplnishmentQuery($groupableClass = Frame::class, $locationId = null, $vendorId = null)
    {
        $tagCountSql = "(SELECT rfid_tags.item_id, rfid_tags.location_id, count(distinct rfid_tags.id) as qty_on_hand
      FROM rfid_tags
      WHERE rfid_tags.status in ('ACTIVE', 'UNASSOCIATED', 'RETAG')
        AND rfid_tags.deleted_at is null
        AND rfid_tags.detail_type = '".$groupableClass."'
      group by rfid_tags.item_id, rfid_tags.location_id) AS tag_count";

        if ($locationId == null || $locationId == 0) {
            $allLocationSelectQuery = DB::table('items')
                ->select([
                    'items.id as item_id', 'items.vendor_id as vendor_id', DB::raw('null as location_id'),
                    'items.min_qty as min_qty', 'items.min_order as min_order',
                    DB::raw("IFNULL(tag_count.qty_on_hand, 0) as qty_on_hand"),
                    DB::raw("(IFNULL(sum(purchase_sub_orders.qty),0)-IFNULL(sum(purchase_sub_order_receives.qty),0)) as qty_on_order"),
                ])
                ->leftJoin(DB::raw($tagCountSql), function ($join) {
                    $join->on("items.id", "=", "tag_count.item_id");
                })
                ->leftJoin("purchase_sub_orders", function ($join) {
                    $join->on("items.id", "=", "purchase_sub_orders.item_id")
                        ->where("purchase_sub_orders.order_for", "=", "Inventory")
                        ->whereNull("purchase_sub_orders.deleted_at");
                })
                ->leftJoin("purchase_sub_order_receives", function ($join) {
                    $join->on("purchase_sub_orders.id", "=", "purchase_sub_order_receives.purchase_sub_order_id")
                        ->whereNull("purchase_sub_order_receives.deleted_at");
                })
                ->whereNull("items.deleted_at")
                ->where("items.min_qty", ">", 0)
                ->where('items.groupable_type', '=', $groupableClass)
                ->whereNotIn("items.id", function ($query) {
                    $query->select('item_id')->from("item_locations")
                        ->where("min_qty", '>', 0)
                        ->whereNull("deleted_at");
                })
                ->when($vendorId > 0, function ($qry) use ($vendorId) {
                        return $qry->where("items.vendor_id", $vendorId);
                })
                ->groupBy(['items.id'])
                ->having('min_qty', ">", DB::raw("(IFNULL(qty_on_hand, 0)+qty_on_order)"));
        }

        $locationBaseSelectQuery = DB::table('items')
            ->select([
                'items.id as item_id', 'items.vendor_id as vendor_id', 'item_locations.location_id as location_id',
                'item_locations.min_qty as min_qty', 'item_locations.min_order as min_order',
                DB::raw("IFNULL(tag_count.qty_on_hand,0) as qty_on_hand"),
                DB::raw("(IFNULL(sum(purchase_sub_orders.qty),0)-IFNULL(sum(purchase_sub_order_receives.qty),0)) as qty_on_order"),
            ])
            ->join("item_locations", function ($join) {
                $join->on("item_locations.item_id", "=", "items.id")
                    ->whereRaw("item_locations.min_qty > 0")
                    ->whereNull("item_locations.deleted_at");
            })
            ->leftjoin("locations", "locations.id", "=", "item_locations.location_id")
            ->leftJoin(DB::raw($tagCountSql), function ($join) {
                $join->on("item_locations.location_id", "=", DB::raw("tag_count.location_id"))
                    ->where("item_locations.item_id", "=", DB::raw("tag_count.item_id"));
            })
            ->leftJoin("purchase_sub_orders", function ($join) {
                $join->on("item_locations.location_id", "=", "purchase_sub_orders.request_location_id")
                    ->where("purchase_sub_orders.item_id", "=", DB::raw("item_locations.item_id"))
                    ->where("purchase_sub_orders.order_for", "=", "Inventory")
                    ->whereNull("purchase_sub_orders.deleted_at");
            })
            ->leftJoin('purchase_sub_order_receives', function ($join) {
                $join->on('purchase_sub_orders.id', '=', 'purchase_sub_order_receives.purchase_sub_order_id')
                    ->where('purchase_sub_orders.request_location_id', '=',
                        DB::raw('purchase_sub_order_receives.request_location_id'))
                    ->whereNull('purchase_sub_order_receives.deleted_at');
            })
            ->whereNull('items.deleted_at')
            ->where('items.groupable_type', '=', $groupableClass)
            ->when($locationId > 0, function ($qry) use ($locationId) {
                        return $qry->where("item_locations.location_id", $locationId);
            })
            ->when($vendorId > 0, function ($qry) use ($vendorId) {
                        return $qry->where("items.vendor_id", $vendorId);
            })
            ->groupBy(['item_locations.location_id', 'item_locations.min_qty', 'item_locations.min_order', 'items.id'])
            ->having('item_locations.min_qty', ">", DB::raw("(IFNULL(qty_on_hand, 0)+qty_on_order)"));

        $finalQuery = $locationBaseSelectQuery;

        if ($locationId === null) {
            $finalQuery = $locationBaseSelectQuery->union($allLocationSelectQuery);
        } elseif ($locationId === 0) {
            $finalQuery = $allLocationSelectQuery;
        }

        return $finalQuery->orderBy('item_id');
    }```
when I check the result, it comes without having a clause filter.
Can someone please tell me what I am doing wrong? 
### Steps To Reproduce:

<!-- If possible, please provide a GitHub repository to demonstrate your issue -->
<!-- laravel new bug-report --github="--public" -->

[bert-w]

Perhaps you should consider not using the query builder, so you run the whole statement as a single string (beware of any user input you might use here). The given code is pretty much unreadable, and by the looks of it your query builder conversion did not provide you any benefits.

[atapatel]

@bert-w do mean use DB::raw() ?