0

I have a custom joomla MVC component. The component has a table of items, and a table of bids to deliver each item. An item can have multiple bids.

i need to show a COUNT of the bids on the items LIST view within each row of the foreach.

What is the BEST way of achieving this? I have tried adding the following to the items model but I am stumped at how to define $id for each item row.

public function getBidsByItemId() { 
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select('COUNT(*)');
    $query->from($db->quoteName('#__table_bids'));
    $query->where($db->quoteName('item_id')." = ".$id);

    // Reset the query using our newly populated query object.
    $db->setQuery($query);
    $count = $db->loadResult();

}

Here you can see the full component/models/items.php to which I added it: http://ideone.com/yPJHRk

Grateful for help from the MVC experts out there.

larpo
  • 1,073
  • 1
  • 10
  • 18

2 Answers2

0

The best way would probably be a JOIN. Try adding in line 83 something like the following (you'll have to adapt it to your db table structure):

// Join over the fields by field 'item_id'
$query->select('itemBids.bids AS bidsNum');
$query->join('LEFT', '#__entrusters_bids AS itemBids ON itemBids.item_id = a.item_id');

Then you'll be able to fetch the value from the object. I can't test it to be more specific to your problem so tell me if it works.

ilias
  • 1,345
  • 1
  • 9
  • 16
0

you should definently make a join, but from what I understand what you want is the bid-count? In that case you should make a group-by query, similar to what ilias is saying, but: 

$query->select('count(itemBids.bids) AS bidsNum');
$query->join('LEFT', '#__entrusters_bids AS itemBids ON itemBids.item_id = a.id');
$query->group('a.id');

also notice to the reference change in the on-statement in the join,

regards Jonas

jonasfh
  • 4,151
  • 2
  • 21
  • 37