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

Laravel 10 – Complex product search based on article number in the product option tables

After retrieving the first article number, I can only obtain the data for all products from the query. Each product has a "type_id" that determines in which junction table the product options can be found. My question is how can I specify conditions in the "->select()" parameter to ensure that only the value of the specific field from the corresponding junction table is retrieved each time.

The search query looks like this.

public function ProductQuery($queryValue)
{
    $product = Product::leftjoin('option_tables_one', 'products.id', '=', 'option_tables_one.product_id')
    ->leftjoin('option_tables_two', 'products.id', '=', 'option_tables_two.product_id')
    ->leftjoin('option_tables_three', 'products.id', '=', 'option_tables_three.product_id')
    ->leftjoin('product_rows_sgsf', 'products.id', '=', 'product_rows_sgsf.product_id')
    ->select(
        'products.*',

        'option_tables_one.item_number',
        'option_tables_one.sales_price_netto',
        'option_tables_one.stock',

        'option_tables_two.item_number',
        'option_tables_two.sales_price_netto',
        'option_tables_two.stock',

        'option_tables_three.item_number',
        'option_tables_three.sales_price_netto',
        'option_tables_three.stock',
    )
    ->where('option_tables_one.item_number', 'LIKE', '%' . $queryValue. '%')
    ->orWhere('option_tables_two.item_number', 'like', '%' . $queryValue . '%')
    ->orWhere('option_tables_three.item_number', 'like', '%' . $queryValue . '%')
    ->get();

    return $product;
}

If the product’s "type_id" is equal to 1, then within the "->select()" parameter, only these fields should be queried:

->select(
    'products.*',

    'option_tables_one.item_number',
    'option_tables_one.sales_price_netto',
    'option_tables_one.stock',
)

If the product’s "type_id" is equal to 2, then within the "->select()" parameter, only these fields should be queried:

->select(
    'products.*',
    
    'option_tables_two.item_number',
    'option_tables_two.sales_price_netto',
    'option_tables_two.stock',
)

And so on…

I have a shorter query that produces the same result as the one seen above:

public function ProductQuery($queryValue)
{
    $product = ProductOptionTableOne::with('product')->where('item_number', 'LIKE', "%{$queryValue}%")->first();
    $product .= ProductOptionTableTwo::with('product')->where('item_number', 'LIKE', "%{$queryValue}%")->first();
    $product .= ProductOptionTableThree::with('product')->where('item_number', 'LIKE', "%{$queryValue}%")->first();
    
    return $product;
}
Tags:

2

Answers


  1. 0

    Laravel’s query builder does not directly support conditional joins or selects within the same query, so you’ll have to build the query dynamically based on the type_id.

    Use addSelect method instead of select to avoid overriding the previously added select statements. This way, you can dynamically build your select statement based on the conditions.

    public function ProductQuery($queryValue)
{
    $query = Product::query();

    // Add joins and selects conditionally based on type_id

    $query->when($type_id == 1, function ($q) {
        return $q->leftJoin('option_tables_one', 'products.id', '=', 'option_tables_one.product_id')
                 ->addSelect('option_tables_one.item_number', 'option_tables_one.sales_price_netto', 'option_tables_one.stock');
    });

    $query->when($type_id == 2, function ($q) {
        return $q->leftJoin('option_tables_two', 'products.id', '=', 'option_tables_two.product_id')
                 ->addSelect('option_tables_two.item_number', 'option_tables_two.sales_price_netto', 'option_tables_two.stock');
    });

    // Add more conditions

    // Add the base product fields and the where conditions
    $query->addSelect('products.*')
          ->where(function ($q) use ($queryValue) {
              $q->where('option_tables_one.item_number', 'LIKE', '%' . $queryValue . '%')
                ->orWhere('option_tables_two.item_number', 'LIKE', '%' . $queryValue . '%')
                // Add more orWhere conditions for other tables if needed
                ;
          });

    return $query->get();
}
    Login or Signup to reply.
  2. 0

    You can use mysql CASE statement

    e.i.

    ->select(
    'products.*',
    IlluminateSupportFacadesDB::raw("
        CASE 
            WHEN products.type_id = 2 THEN option_tables_two.item_number 
            WHEN products.type_id = 3 THEN option_tables_three.item_number   
        ELSE option_tables_one.item_number END AS item_number
    "),
    IlluminateSupportFacadesDB::raw("
        CASE 
            WHEN products.type_id = 2 THEN option_tables_two.sales_price_netto 
            WHEN products.type_id = 3 THEN option_tables_three.sales_price_netto   
        ELSE option_tables_one.sales_price_netto END AS sales_price_netto
    "),
    IlluminateSupportFacadesDB::raw("
        CASE 
            WHEN products.type_id = 2 THEN option_tables_two.stock 
            WHEN products.type_id = 3 THEN option_tables_three.stock   
        ELSE option_tables_one.stock END AS stock
    "),
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search