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

Question

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;
}

Answer 1

You can use mysql CASE statement

e.i.

->select(
    'products.*',
    \Illuminate\Support\Facades\DB::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
    "),
    \Illuminate\Support\Facades\DB::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
    "),
    \Illuminate\Support\Facades\DB::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
    "),
)