2

I need a hint for this problem, trying for days already to solve it:

THE PROBLEM I am working with an Joomla extension which has this 2 tables of interest:

  1. COMPANIES
  2. PRODUCTS

A module lists all companies. Now I need to add to each of this displayed companies the cheapest Price of its products, along with some other values connected to this cheapest price (in the same db row).

So I JOIN the MySQL-Query for the Company-Array to the products table and find out the MIN price:

EDIT

$db = $this->getDbo();
$query = $db->getQuery(true);

// QUERY TO DISPLAY THE COMPANIES

SELECT a.id, a.title AS CompanyName, a.compdiscount AS CompanyDiscount,
p.min_price AS MinimalPrice, p.company_id, p.title AS Product, p.price AS
Price, p.discount AS ProductDiscount, p.lowprice AS Lowprice, p.peakprice
AS Peakprice 
FROM `company2` AS a 

//SUBQUERY TO PRODUCTS 
LEFT JOIN (SELECT MIN(price) AS
min_price, company_id, title, price, discount, lowprice, peakprice 
FROM products2 GROUP BY company_id) AS p 
ON p.company_id = a.id 
GROUP BY a.id; 

I have created a Fiddle:

https://www.db-fiddle.com/f/w7fFNS78mCjQCiEvpqdeGd/0

THE (STILL NOT WORKING) RESULT In the fiddle I have created 4 tables: 1.+2. Real-data tables 3.+4. Reduced copies of these tables

And 4 Queries: 1.+3. Just show all data 2. Try to use the subquery with the real-data tables - NOT WORKING (the output is correct in displaying 3 companies and their minnimum price, but the other values are not from the same rows 4. Use the subquery with the test-data - WORKING - displaying 3 companies with their cheapest price and the values of the same rows.

0

3 Answers 3

1

Follow any of the techniques demonstrated on https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html (just be sure to invert the logic to ensure min values instead of max values) to create a subquery to LEFT JOIN on.

There will be dozens of posts on this topic on the sess pool that is the SQL tag on Stack Overflow such as:

I believe your SQL attempt fundamentally fell down because you were SELECTing MIN with other values (without controlling where within the GROUP's aggregate the data is coming from).

Raw SQL: (DB-Fiddle)

SELECT c.id,
       c.title         AS CompanyName,
       c.contact_email AS CompanyDiscount,
       p.title         AS Product,
       p.price         AS Price,
       p.prodiscount   AS ProductDiscount,
       p.lowprice      AS Lowprice,
       p.peakprice     AS Peakprice 
FROM jos4py_cddir_content AS c
LEFT JOIN (
      SELECT p1.*
      FROM jos4py_cddir_products      AS p1
      LEFT JOIN jos4py_cddir_products AS p2 ON p1.company_id = p2.company_id
                                               AND p1.price > p2.price
      WHERE p2.price IS NULL
) AS p ON c.id = p.company_id

Here is my untested attempt at Joomla syntax with some additional bits and pieces to help with diagnostics. Please do not ever present raw query or error details on your public site.

try {
    $db = JFactory::getDbo();

    $min_subquery = $db->getQuery(true)
                       ->select("p1.*")
                       ->from("#__cddir_products AS p1")
                       ->leftJoin("#__cddir_products AS p2 ON p1.company_id = p2.company_id AND p1.price > p2.price")
                       ->where("p2.price IS NULL");

    $query = $db->getQuery(true)
                ->select("c.id,
                          c.title         AS CompanyName,
                          c.contact_email AS CompanyDiscount,
                          p.title         AS Product,
                          p.price         AS Price,
                          p.prodiscount   AS ProductDiscount,
                          p.lowprice      AS Lowprice,
                          p.peakprice     AS Peakprice")
                ->from("#__cddir_content AS c")
                ->leftJoin("($min_subquery) AS p ON c.id = p.company_id");

    $db->setQuery($query);
    JFactory::getApplication()->enqueueMessage($query->dump(), 'info');
    if (!$result = $db->loadAssocList()) {
        echo "<p>No Rows Found</p>";
    } else {
        echo "<pre>";
        var_export($result);
        echo "</pre>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // don't show $e->getMessage() to public
}
0
1

I assume that when you join the companies and products you don't really mean a.id = p.id?

Assuming that the product table has a company_id then you could do a generic subquery approach like the following. Not the most efficient but easy to think. The essential point being, you can't just join to the min of another table, but you can use a subquery to join to the subset of the table that you want.

$db = $this->getDbo();
$query = $db->getQuery(true);

$query->select('a.id, a.title, p.min_price, p.discount, p.morevalues');
$query->from('#__company AS a');

// join to the result of the subquery
$query->join('LEFT', '(SELECT MIN(price) as min_price, company_id, discount, morevalues FROM #__products GROUP BY company_id) AS p', 'p.company_id = a.id');
$query->group('a.id');

$db->setQuery($query, 0, $params->limit);
$items = $db->loadObjectList();
return $items;
1
  • Sure, you are totally right -> a.id = company_id ... sleepless nights are neither good for the eyes nor the brain :) Your solution works perfect, thanks a lot. Unfortuantelly it works only with a test enviroiment (which is actually just a shortened version of the real data), but not with the real data. And I have no clue why. Would you like to have a look at my edited OP and see if you can see, what I am missing?
    – Steven
    Apr 24, 2023 at 11:38
0

WORKING SOLUTION!!

The answer of mickmackusa brought the solution, just needed to adopt some things and get the correct Joomla syntax to make the code running. Posting the running example for anyone who could use it:


$db = JFactory::getDbo();

    $subquery = $db->getQuery(true);
                $subquery->select('p1.*');
                $subquery->from($db->quoteName('#__products') . ' AS p1');
                $subquery->join('LEFT', '#__products AS p2 
                        ON (p1.company_id = p2.company_id AND p1.price > p2.price)');
                $subquery->where('p2.price IS NULL');
   
    $query = $db->getQuery(true);
                
                $query->select('
                        a.id                AS id,
                        a.whatever_x        AS whatever_x,
                        p.title             AS Product,
                        p.price             AS price,
                        p.lowprice          AS lowprice,
                        p.whatever_y        AS whatever_y');
 

    $query->from($db->quoteName('#__content') . ' AS a');
    $query->join('LEFT', '(' . $subquery . ')' . ' AS p ON a.id = p.company_id');
  

    $db->setQuery($query, 0, $params->limit);
    $items = $db->loadObjectList();

        return $items;

Additional info: I did not know about the very helpful trick with throwing out SQL statements posted by mickmackusa. For every beginner like me: Put your code inside of this construction:

// start 
try {

// put here whatever SQL 

// close it with this code
    $db->setQuery($query);
    JFactory::getApplication()->enqueueMessage($query->dump(), 'info');
    if (!$result = $db->loadAssocList()) {
        echo "<p>No Rows Found</p>";
    } else {
        echo "<pre>";
        var_export($result);
        echo "</pre>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  
}

// don't show $e->getMessage() to public!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.