Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – sql filter from another table

Hello everyone i am developing a website that listing and filtering products.

My problem is products table is have 40000 row and product_details have 2.1 million row.

I want to filter products table with product_details table

my code is

$sliced = array_values(array_slice($req_val, 3));
    
        $products = Products::where('status', 1)
            ->where('s_part', $req_val[0])
            ->where('main_category', $req_val[1])
            ->where('sub_category', $req_val[2])
            ->join('product_details', 'products.sapcode', '=', 'product_details.sapcode')
            ->where(function ($query) use ($sliced, $keys) {
                foreach ($sliced as $index => $value) {
                    $key = $keys[$index];
                    $query->orWhere(function ($query) use ($value, $key) {
                        $query->where('product_details.value', $value)
                            ->where('product_details.name', $key);
                    });
                }
            })
            ->get('products.sapcode');
        
        $products = $products->map(function($item){
            return $item->sapcode;
        });
    
        $counts = array_count_values($products->toArray());
    
        $threshold = count($sliced);
    
        $filtered = array_filter($counts, function($count) use ($threshold) {
            return $count >= $threshold;
        });
    
        $result = array_keys($filtered);
    
        $products = Products::whereIn('products.sapcode', $result)->get();
        return response()->json($products);

Products model connected to products table
Product_details is connected to product_details

How to connect and filter this two table. What is the best way to do it.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was working on this situation and solve it.

    $filteredProducts = ProductS::select('products.sapcode', 'products.status', 'products.TR', 'products.EN', 'products.module')
            ->join('product_details', 'products.sapcode', '=', 'product_details.sapcode')
            ->where('products.status', 1)
            ->where('products.s_part', $req_val[0])
            ->where('products.main_category', $req_val[1])
            ->where('products.sub_category', $req_val[2])
            ->where(function ($query) use ($sliced, $keys) {
                foreach ($sliced as $index => $value) {
                    $query->orWhere(function ($query) use ($keys, $value, $index) {
                        $query->where('product_details.name', $keys[$index])
                            ->where('product_details.value', $value);
                    });
                }
            })
            ->groupBy('products.sapcode', 'products.status', 'products.TR', 'products.EN', 'products.module')
            ->havingRaw('COUNT(*) >= '.count($sliced));

    havingRaw is check for sapcode childs for unique filtered product.


  2. 0

    One way to do it is how you are doing it know: An eloquent builder with a join.
    I think an easier to read solution would be defining the relations in your models. I guess you have 1:M or N:M relations here, so use hasMany, belongsToMany/belongstoOne, then you can use whereHas like this:

    ->wehereHas('productDetails', function(Builder $query) {
    // here you filter by product details
    $query->where('column', $value);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search