I have a table of defects that users are reported, but as a little offshoot, I am going to take all the names and numbers of people who have reported a fault and create a directory of users and reports.
I have already created the reporterdirectory layout, but am having problems ammending the query
case 'defects.reporterdirectory':
//BASE FIELDS
$query->select(
'DISTINCT a.number,'
. 'a.id,'
. 'a.depot,'
. 'a.name,'
. 'a.toc');
$this->addGroupBy('a.number'); //added by me
break;
Now if I feed in this query into PHP
SELECTDISTINCT a.number, a.id, a.depot, a.name, a.toc
FROM app01_traindefectlogger_defects AS a
WHERE (
a.published =1
OR a.published ISNULL
OR a.published =0
)
GROUPBY a.number
it returns the correct data.. but the altered MODEL above, the GROUPBY function is not functioning as antipated.
In the debugger, the query is being returned as
SELECT DISTINCT a.number,a.id,a.depot,a.name,a.toc
FROM app01_traindefectlogger_defects AS a
WHERE (a.published = 1 OR a.published IS NULL OR a.published = 0)
ORDER BY a.number,a.carriage asc
i.e it is NOT listing the GroupBy
How do I add an SQL GROUP BY function to the model so it mimics the SQL above. If you do not have the group by function, it just lists every record with multiple duplicates.
