Skip to main content
added 26 characters in body
Source Link
jamesgarrett
  • 3.3k
  • 1
  • 12
  • 17

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.pricemin_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;

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

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;
Source Link
jamesgarrett
  • 3.3k
  • 1
  • 12
  • 17

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