How to use the value of array inside DB raw Laravel

Question

Hi I have question about DB raw in Laravel. Actually now I have a variable named $currencies with the structure like this.

<?php

$currencies = [
    'CNY' => 1000,
    'USD' => 10000
]

And I need to use the value of that array for sorting my products table. With the products table looks like this:

| id  |  product_name  | original_currency  | price |
|  1  | lorem ipsum d  | CNY                | 20    |
|  2  | new product n  | USD                | 10    |

So far my code looks like this:


$products = DB::raw(SELECT product_name, 
                           price, 
                           price * $currencies[original_currency] AS converted_price 
                    FROM products);

But I got the error Undefined Index: original_currency

I just need to access the correct value to set the converted_price. So the expected result will looks like this:

| id  |  product_name  | original_currency  | price |  converted_price  |
|  1  | lorem ipsum d  | CNY                | 20    |  20000            |
|  2  | new product n  | USD                | 10    |  1000000          |

That's not even valid php code. There are no ' or " surrounding the query. — Jerodev
– Jerodev, Commented Aug 26, 2019 at 9:35
Also see Why should I provide a Minimal Reproducible Example for a very simple SQL query? — Raymond Nijland
– Raymond Nijland, Commented Aug 26, 2019 at 10:02

Dark Knight · Accepted Answer · 2019-08-26 17:04:54Z

Issues:

Pass query as a string, missing single or double quotes around the query.
You can't directly use PHP array inside queries, because queries are being sent to MySQL server and that doesn't have access to your PHP variables.

Solutions:

Approach 1: Fetch simple data from MySQL, modify data in PHP

    $products = DB::raw('SELECT products.product_id, products.original_currency, products.price FROM products);

    foreach($products as $key => $value) {
        $value['converted_price'] = $value['price'] * $currencies[$value['original_currency']];
    }

Approach 2: Use Temp table in MySQL:

    SELECT 
        products.product_id,
        products.price,
        products.price * weight_table.weight AS converted_price
    FROM products
    INNER JOIN (
        SELECT 'CNY' AS currency, 100 AS weight
        UNION
        SELECT 'USD' AS currency, 1000 AS weight
    ) AS weight_table 
        ON products.original_currency = weight_table.currency;

If you have dynamic currency array in PHP, use the following code to generate inner temp table.

$currencies = [
    'CNY' => 100,
    'USD' => 1000
];

function getTempTable($arrCurrency) {
    $arrQuery = [];
    foreach($arrCurrency as $key => $value) {
        $arrQuery[] = "SELECT '" . $key . "' AS currency, " . $value . " AS weight";
    }

    return implode(PHP_EOL. " UNION " . PHP_EOL, $arrQuery); 
}

$strQuery = 'SELECT 
    products.product_id,
    products.price,
    products.price * weight_table.weight AS converted_price
FROM products
INNER JOIN ( ' . getTempTable($currencies) . ') AS weight_table
    ON products.original_currency = weight_table.currency';

$products = DB::raw($strQuery);

The solution looks fine, but this could potentially be simplified using a macro on the Illuminate\Database\Query\Builder class.

Collectives™ on Stack Overflow

How to use the value of array inside DB raw Laravel

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related