Costum Report with two queries or UNION

Questions
1

Silverstripe Version: 4.5.1

Question:
Hey there, I’m struggeling with a custom report. What I want to do is to execute a SQL statement which includes UNION. Unfortunalty, Silverstripe ORM does not support UNIONs :worried:

So my first thought was to merge or combine two seperate queries (I’m extending ShopPeriodReport):

class CustomProductReport extends ShopPeriodReport
{
    protected $title = 'Custom Product Report';

    protected $description = 'Custom Product Report Description.';

    protected $dataClass = Product::class;

    protected $periodfield = '"SiteTree"."Created"';

    public function query($params)
    {
        $query1 = $this->query1($params);
        $query2 = $this->query2($params);
        $query = // How to combine the two queries ?

        return $query;
     }

    public function query1($params){
        $query = parent::query($params);
        $query->selectField($this->periodfield, 'FilterPeriod');
        $query->addSelect(
            [
                '"SilverShop_Product"."ID"',
                '"SiteTree"."ClassName"',
                '"SiteTree"."Title"',
                '"SilverShop_Product"."BasePrice"',
                '"SiteTree"."Created"',
            ])
            ->selectField('SUM("SilverShop_OrderItem"."Quantity")', 'Quantity')
            ->selectField('SUM("SilverShop_OrderAttribute"."CalculatedTotal")', 'Sales')
            ->addInnerJoin('SiteTree', '"SilverShop_Product"."ID" = "SiteTree"."ID"')
            ->addLeftJoin('SilverShop_Product_OrderItem', '"SilverShop_Product"."ID" = "SilverShop_Product_OrderItem"."ProductID"')
            ->addLeftJoin('SilverShop_OrderItem', '"SilverShop_Product_OrderItem"."ID" = "SilverShop_OrderItem"."ID"')
            ->addLeftJoin('SilverShop_OrderAttribute', '"SilverShop_Product_OrderItem"."ID" = "SilverShop_OrderAttribute"."ID"')
            ->addLeftJoin('SilverShop_Order', '"SilverShop_OrderAttribute"."OrderID" = "SilverShop_Order"."ID"')
            ->addGroupby('"SilverShop_Product"."ID"');

        return $query;
    }

    public function query2($params){
        $query = parent::query($params);
        $query->selectField($this->periodfield, 'FilterPeriod');
        $query->addSelect(
            [
                '"SilverShop_Product"."ID"',
                '"SiteTree"."ClassName"',
                '"SiteTree"."Title"',
                '"SilverShop_Product"."BasePrice"',
                '"SiteTree"."Created"',
            ])
            ->selectField('SUM("B2BOrdersItem"."Quantity")', 'Quantity')
            ->selectField('SUM("B2BOrdersItem"."Quantity")*"SilverShop_Product"."BasePrice"', 'Sales')
            ->addInnerJoin('SiteTree', '"SilverShop_Product"."ID" = "SiteTree"."ID"')
            ->addLeftJoin('B2BOrdersItem', '"SilverShop_Product"."ID" = "B2BOrdersItem"."ProductID"')
            ->addLeftJoin('BackendOrder', '"B2BOrdersItem"."BackendOrderID" = "BackendOrder"."ID"')
            ->addWhere('"BackendOrder"."Placed" IS NOT NULL')
            ->addGroupby('"SilverShop_Product"."ID"');

        return $query;
    }

(Both queries are working when I return them seperatly)

My second thought was to execute the raw sql statement wich returns in phpmyadmin the correct result:

SELECT p.ID, SiteTree.ClassName, SiteTree.Title, p.BasePrice, SiteTree.Created, SUM(boi.Quantity) as Quantity, SUM(boi.Quantity)*p.BasePrice as Sales
FROM SiteTree
INNER JOIN SilverShop_Product as p ON SiteTree.ID = p.ID
LEFT JOIN B2BOrdersItem as boi ON p.ID = boi.ProductID
LEFT JOIN BackendOrder as bo ON boi.BackendOrderID = bo.ID
WHERE bo.Placed IS NOT NULL
GROUP BY p.ID
UNION ALL
SELECT p.ID, SiteTree.ClassName, SiteTree.Title, p.BasePrice, SiteTree.Created, SUM(oi.quantity) as Quantity, SUM(oa.CalculatedTotal) as Sales
FROM SiteTree
INNER JOIN SilverShop_Product as p ON SiteTree.ID = p.ID
LEFT JOIN SilverShop_Product_OrderItem as poi ON p.ID = poi.ProductID
LEFT JOIN SilverShop_OrderItem as oi ON poi.ID = oi.ID
LEFT JOIN SilverShop_OrderAttribute as oa ON poi.ID = oa.ID
LEFT JOIN SilverShop_Order as o ON oa.OrderID = o.ID
GROUP BY p.ID

I also tried to extend Report instead of ShopPeriodReport but this does not work either because the report expects an SS_List and I don’t know how to convert the result into it or push the single rows into an SS_List to return it to get the wished output.

Does anyone have any ideas on how I might approach this?