Does Editor have a GroupBy function?
Does Editor have a GroupBy function?
pansengtat
Posts: 66Questions: 26Answers: 1
Sample code for current Editor instance:
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;
if ( $current_rankID >= $CONST_RANK_EXTERNAL ) {
$data = Editor::inst($db, 'TestRequestActualSchedule', 'TestRequestNo')
->where('TestRequestMain.AssignedGroupID', $current_groupID)
->field(
Field::inst('TestRequestActualSchedule.TestRequestNo')->set(false),
Field::inst('TestRequestMain.EngineerID')->set(false),
Field::inst('TestRequestMain.POnumber')->set(false),
Field::inst('TestRequestMain.ProjectID')->set(false),
Field::inst('TestRequestMain.ProjectName')->set(false),
Field::inst('TestRequestActualSchedule.ActualStartDate'),
Field::inst('TestRequestActualSchedule.ActualEndDate'),
Field::inst('TestRequestActualSchedule.ManHrsNM')->validator('Validate::numeric'),
Field::inst('TestRequestActualSchedule.ManHrsOT')->validator('Validate::numeric'),
Field::inst('TestRequestActualSchedule.ManHrsOTSunPH')->validator('Validate::numeric'),
Field::inst('TestRequestDiscrepancySchedule.DiffManHrsNM')->validator('Validate::numeric'),
Field::inst('TestRequestDiscrepancySchedule.DiffManHrsOT')->validator('Validate::numeric'),
Field::inst('TestRequestDiscrepancySchedule.DiffManHrsOTSunPH')->validator('Validate::numeric'),
Field::inst('TestRequestActualSchedule.TotalManHrs')->validator('Validate::numeric'),
Field::inst('TestRequestMain.Status'),
Field::inst('StatusMirror.String')->set(false),
Field::inst('TestRequestActualSchedule.Approval')->set(false),
Field::inst('ConfirmationMirror.String')->set(false),
Field::inst('TestRequestAdv.Remarks')->set(false),
Field::inst('TestRequestMain.AssignedGroupID')->set(false),
Field::inst('AssignedGroup.Name')->set(false)
)
->leftJoin('TestRequestDiscrepancySchedule', 'TestRequestActualSchedule.TestRequestNo', '=', 'TestRequestDiscrepancySchedule.TestRequestNo')
->leftJoin('TestRequestAdv', 'TestRequestActualSchedule.TestRequestNo', '=', 'TestRequestAdv.TestRequestNo')
->leftJoin('TestRequestMain', 'TestRequestActualSchedule.TestRequestNo', '=', 'TestRequestMain.TestRequestNo')
->leftJoin('Login', 'TestRequestMain.EngineerID', '=', 'Login.ID')
->leftJoin('ConfirmationMirror', 'TestRequestActualSchedule.Approval', '=', 'ConfirmationMirror.ID')
->leftJoin('StatusMirror', 'TestRequestMain.Status', '=', 'StatusMirror.ID')
->leftJoin('AssignedGroup', 'TestRequestMain.AssignedGroupID', '=', 'AssignedGroup.ID')
->process($_POST)
->data();
}
Is it possible to have a GroupBy used with a leftJoin() method in DataTables Editor?
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
Currently no - I'm sorry to say that it doesn't.
GROUP BYmakes things a good bit more difficult on insert and update, which is why it doesn't currently support that SQL option.Options to do something similar might be to loop over the returned data and perform your own data manipulation / query. Or possibly you could query the data and return in the required JSON format, rather than having the Editor class do it, but still have the Editor class perform the update etc.
Regards,
Allan
I am not sure what this means, I need an elaboration of how a GroupBy can be simulated with DataTables.
Example of problem encountered:
Imageshack link
I don't quite understand how the image shows a group by issue. Can you elaborate so I can help resolve that?
Also, I don't appear to have a record of an Editor license for you. Can you confirm the purchase ID for your Editor license so I can update my records please?
Thanks,
Allan
Explanation of sample link
Tables
mainandadvcontain a unique columnRequestID.Table
maincontains a non-unique columnProjectID,while table
advcontains a non-unique-columnProjectPlan.Table
PlannedSchedulecontains a non-unique column to referenceRequestIDfrom bothmainandadv, but tablePlannedSchedulecontains its own auto-incremented/unsigned column calledSerialIDas its primary key.Table
PlannedSchedulealso contains non-unique columnsPlannedDateandPlannedHours.Suppose that there is only 1 entry in
mainandadvcontaining only 1RequestID.However,
PlannedSchedulecontains 3 entries (each with a differentPlannedDate)for the sameRequestID.When a
leftJoin()is used to join these 3 tables, instead of showing 3 entries for the 3 differentPlannedDate. there are 3 x 3 = 9 entries (because of 3 entries per table, which is 3).I discussed with a fellow programmer, who suggested that a
GroupByis required (this was tested on the SQL editor in PhpMyAdMin), however as there is noGroupByis DataTables, the other way is to do the following:Which I wish to clarify for this particular example I am encountering.
I also wish to clarify, if I were to use a
select distinctto solve duplicated entries, how would this be implemented or used with the Editor instance?(As for the record for Editor license, this account that I am using to ask questions has its Editor trial expired, however the version of Editor using in my current project is licensed to a manager, which I don't have credentials to login to use and ask questions like these.)