SQL join

joining.sql

 public function ajax_search(Request $request)
    {
        saveSearchData($request);
        $keywords = array();
        $search_products =
            Product::join('brands', 'products.brand_id', '=', 'brands.id')
            ->join('product_categories', 'product_categories.product_id', '=', 'products.id')
            ->join('product_stocks', 'product_stocks.product_id', '=', 'products.id')
             ->where('products.published', 1)
            ->where('products.approve_status', 1)
            ->where('products.name', 'like', $request->search)->orWhere('products.tags', 'like', '%' . $request->search . '%');

        if ($search_products->active()->get()->count() > 0) {
            $products = $search_products->groupBy('products.id');
        } else {

            $searchValues = explode(' ', $request->search);
            $products = Product::join('brands', 'products.brand_id', '=', 'brands.id')
                ->join('product_categories', 'product_categories.product_id', '=', 'products.id')
                ->join('product_stocks', 'product_stocks.product_id', '=', 'products.id')
                 ->where('products.published', 1)
                ->where('products.approve_status', 1)
                ->where(function ($el) use ($searchValues) {
                    foreach ($searchValues as $searchValue) {
                        $el->where('products.name', 'like', $searchValue . '%');
                        $el->orWhere('products.name', 'like', '% ' . $searchValue . '%');
                        $el->orWhere('products.name', 'like', '% ' . $searchValue . ' %');
                        $el->orWhere('products.name', 'like', $searchValue . ' %');

                        $el->orWhere('products.tags', 'like', '% ' . $searchValue . '%');
                        $el->orWhere('products.tags', 'like', '% ' . $searchValue . '%');
                        $el->orWhere('products.tags', 'like', $searchValue . ' %');
                        $el->orWhere('products.tags', 'like', $searchValue . '%');
                    }
                })->groupBy('products.id');
        }
        $products = $products->select(
            'products.id',
            'products.name',
            'products.tags',
            'product_stocks.selling_price',
            'products.id',
            'products.num_of_sale',
            'products.rating',
            'product_stocks.variant',
            'products.name',
            'products.tags',
            'products.slug',
            'products.brand_id',
            'products.unit_price',
            'products.thumbnail_img',
            'products.description',
            'products.meta_description',
            'products.rating'
        )->orderBy('product_stocks.selling_price', 'DESC')->take(10)->get();



        foreach ($products as $key => $product) {
            foreach (explode(',', $product->tags) as $key => $tag) {
                foreach (explode(' ', $request->search) as $key => $search) {
                    if (stripos($tag, $search) !== false) {
                        if (!in_array(strtolower($tag), $keywords)) {
                            array_push($keywords, trim(ucfirst($tag)));
                        }
                    }
                }
            }
        }

        // array_unique
        $keywords = array_unique($keywords);
        rsort($keywords);
        $tags =  $keywords;



        $categories = Category::whereHas('active_product_categories')->IsPublished()->where('name', 'like', '%' . $request->search . '%')->take(6)->get();

        $shops = Shop::whereIn('user_id', verified_sellers_id())->where('name', 'like', '%' . $request->search . '%')->take(6)->get();

        $brands = Brand::whereHas('activeProducts')->where('name', 'like', '%' . $request->search . '%')->take(6)->get();


        $searchKeyword = $request->search;