Hi,
I have searched the forum if there was already a topic on this, but I could not find it.....
In my project, I fork a backend model because I want to have some data available from another table.
But, in this forked model the column from the other table is a group concat.
I use it like so in my copy/paste prepareQuery function:
//addselect
$this->addSelect('GROUP_CONCAT(_deelnemer_.naam SEPARATOR \' en \') AS `_deelnemer_naam`');
//addjoin
$this->addJoin('`#__trailrides_deelnemers` AS _deelnemer_ ON _deelnemer_.team = a.id', 'LEFT');
//goup on id for the group concat
$this->addGroupOrder('a.id');
The $this->addGroupOrder('a.id'); did not seem to work at all.
Later on in the code there is this part
//GROUP ORDER : Prioritary order for groups in lists
foreach($this->getState('query.groupOrder', array()) as $groupOrder)
$query->order($groupOrder);I think this should be
//GROUP ORDER : Prioritary order for groups in lists
foreach($this->getState('query.groupOrder', array()) as $groupOrder)
$query->group($groupOrder);
At least in my project the data is shown correctly if I change that line.
Can you confirm this is an issue or is there something wrong with my code.
For completeness, here is the entire (almost totally original) prepareQuery function in my forked model
protected function prepareQuery(&$query)
{
$acl = TrailridesHelper::getActions();
//FROM : Main table
$query->from('#__trailrides_teams AS a');
//IMPORTANT REQUIRED FIELDS
$this->addSelect( 'a.id');
switch($this->getState('context', 'all'))
{
case 'teams.default':
//BASE FIELDS
$this->addSelect( 'a.betaald,'
. 'a.factuur_verstuurd,'
. 'a.klassering,'
. 'a.naam,'
. 'a.nummer,'
. 'a.rit');
//SELECT
$this->addSelect('_rit_.naam AS `_rit_naam`');
//Add concatenated info from deelnemers
$this->addSelect('GROUP_CONCAT(_deelnemer_.naam SEPARATOR \' en \') AS `_deelnemer_naam`');
//JOIN
$this->addJoin('`#__trailrides_ritten` AS _rit_ ON _rit_.id = a.rit', 'LEFT');
$this->addJoin('`#__trailrides_deelnemers` AS _deelnemer_ ON _deelnemer_.team = a.id', 'LEFT');
//GROUPBY for the group concat
$this->addGroupOrder('a.id');
break;
case 'teams.modal':
//BASE FIELDS
$this->addSelect( 'a.naam');
break;
case 'all':
//SELECT : raw complete query without joins
$this->addSelect('a.*');
// Disable the pagination
$this->setState('list.limit', null);
$this->setState('list.start', null);
break;
}
//FILTER - Access for : Root table
//WHERE - FILTER : Rit
if((int)$this->getState('filter.rit') > 0)
$this->addWhere("a.rit = " . (int)$this->getState('filter.rit'));
//WHERE - FILTER : Klassering
if($this->getState('filter.klassering') !== null)
$this->addWhere("a.klassering = " . $this->_db->Quote($this->getState('filter.klassering')));
//WHERE - FILTER : Betaald
if($this->getState('filter.betaald') !== null)
$this->addWhere("a.betaald = " . (int)$this->getState('filter.betaald'));
//WHERE - FILTER : Factuur verstuurd
if($this->getState('filter.factuur_verstuurd') !== null)
$this->addWhere("a.factuur_verstuurd = " . (int)$this->getState('filter.factuur_verstuurd'));
//Populate only uniques strings to the query
//SELECT
foreach($this->getState('query.select', array()) as $select)
$query->select($select);
//JOIN LEFT
foreach($this->getState('query.join.left', array()) as $join)
$query->join('LEFT', $join);
//JOIN INNER
foreach($this->getState('query.join.inner', array()) as $join)
$query->join('INNER', $join);
//JOIN OUTER
foreach($this->getState('query.join.outer', array()) as $join)
$query->join('OUTER', $join);
//WHERE
foreach($this->getState('query.where', array()) as $where)
$query->where($where);
//GROUP ORDER : Prioritary order for groups in lists
foreach($this->getState('query.groupOrder', array()) as $groupOrder)
$query->group($groupOrder);
//ORDER
foreach($this->getState('query.order', array()) as $order)
$query->order($order);
//ORDER
$orderCol = $this->getState('list.ordering');
$orderDir = $this->getState('list.direction', 'asc');
if ($orderCol)
$query->order($orderCol . ' ' . $orderDir);
} 
