CODEWITHSUNDEEP
phpmysql
Karem
Karem

Reputation: 18103

Grabbing the correct price column from MySQL without having extra queries and php to do it

Following from this memory limit problem:

Grabbing 190k rows from MySQL using PHP, blank page? Memory or buffer?

One of the big reasons I reach memory limit today is because of this script:

When I grab reference numbers (which has costs in my application), I would like to grab the price (what it costs).

A reference number price can be found in deal.price, BUT if the reference number/order is a product, I need to find the price in deals_products.price.

Here is my query I start with:

SELECT `refnumbers`.`refno`, `deals`.`price`, `refnumbers`.`order_id` FROM `refnumbers` JOIN `deals` ON (`deals`.`ID` = `refnumbers`.`deal_id`)

Then, to get the price, I always need this piece of code (which I would like help from you, to remove so it all can be found in the query)

foreach($refnumbers as $ref)
{
    $isProduct = DB::select('product_id')->from('orders_chosenoptions')
    ->where('order_id', '=', $ref['order_id'])->execute()->get('product_id', 0);

    if($isProduct > 0)
    {
        $price = DB::select('price')->from('deals_products')
        ->where('id', '=', $isProduct)
        ->execute()->get('price');

    }else{
        $price = $ref['price'];

    }
}

(I am using Kohana Query Builder, but the query should be readable to know whats going on)

So you can see that this is very ineffective, 1) i run a query, 2) i go through each row and run two queries to be sure I get the correct price.

And when I go through 190k rows, it basically runs 2x190k single queries as extra

How can i possibly skip this, and have the correct price, right from the first query?

Any answer is appreciated.

Note I cannot modify the db design structure.

UPDATE:

I have tried myself successfully with this query:

SELECT `refnumbers`.`refno`, 
CASE WHEN orders_chosenoptions.order_id IS NOT NULL
THEN (SELECT price FROM deals_products WHERE id = orders_chosenoptions.product_id)
ELSE deals.price
END AS price, 
`refnumbers`.`order_id` 
FROM `refnumbers` 
JOIN `deals` ON (`deals`.`ID` = `refnumbers`.`deal_id`) 
LEFT JOIN orders_chosenoptions ON (orders_chosenoptions.order_id = refnumbers.order_id)

Looks like its working, but will some rows be left out? Have i done it wrong?

Upvotes: 0

Views: 67

Answers (1)

marekful
marekful

Reputation: 15351

As far as I understand (w/o table definitions), you are dealing with 4 tables. To achieve what you want in one query, you have to join all tables and test orders_chosenoptions.product_id using MySQL's IF() to conditionally select either deals_products.price OR deals.price.

SELECT r.refno, IF(cp.product_id > 0, dp.price, d.price) AS price, r.order_id 

FROM refnumbers r 

JOIN deals d ON d.ID = r.deal_id

LEFT JOIN orders_chosenoptions oc ON r.order_id = oc.order_id

LEFT JOIN deals_product dp ON oc.product_id = dp.id

WHERE r.deal_id = ...

Upvotes: 2

Related Questions