Super sub query implements join query gracefully

demand

The above activity list needs to be associated with the activity classification table , Commodity table and query conditions accordingly . How to gracefully implement queries with relatively good performance for different conditions

For example, we should vaguely search the activity name from the activity table 、 Search the product name vaguely from the product list 、 Filter according to activity classification

Code

We are service Layer implementation

public function getList(array $params)
    {
    
        // Activity subquery 
        $actSub = $this->actRepo->where('store_id', $this->storeId);
        // Commodity sub query 
        $goodsSub = $this->goodsRepo->select(['goods_id', 'goods_name']);
        // The name of the event 
        if (!empty($params['title'] ?? '')) {
    
            $actSub->where('title', 'like', '%' . $params['title'] . '%');
        }
        // Name of commodity 
        if (!empty($params['goods_name'] ?? '')) {
    
            $goodsSub->where('goods_name', 'like', '%' . $params['goods_name'] . '%');
        }
        // classification 
        if (!empty($params['cate_id'] ?? '')) {
    
            $actSub->where('cate_id', $params['cate_id']);
        }
        // state 
        if (isset($params['status'])) {
    
            $actSub ->where('status', $params['status']);
        }
        //join  Associate two subqueries 
        $query = $this->actRepo->select('activity.*', 'goods.goods_name')
            ->fromSub($actSub, 'activity');
        $query->joinSub($goodsSub, 'goods', function ($join) {
    
            $join->on('activity.goods_id', '=', 'goods.goods_id');
        });
        // In reverse order 
        $query->orderBy('activity.id', 'desc');

        $acts = PaginationHelper::appLists($query);

        return $acts;
    }

Our idea is to add sub queries , Each table creates a subquery , This performance is better , And it can insert the corresponding condition query gracefully

Summary

Of course, implementing this list is a piece of cake for everyone , Maybe you already know this way of writing , I'm kidding ~, I have also written this kind of list natively , But I haven't thought about it before using the framework's own sub query