Does Editor have a GroupBy function?

pansengtat

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?

allan

Currently no - I'm sorry to say that it doesn't. GROUP BY makes 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

pansengtat

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.

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

allan

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

pansengtat

Explanation of sample link

Tables main and adv contain a unique column RequestID.

Table main contains a non-unique column ProjectID,
while table adv contains a non-unique-column ProjectPlan.

Table PlannedSchedule contains a non-unique column to reference RequestID from both main and adv, but table PlannedSchedule contains its own auto-incremented/unsigned column called SerialID as its primary key.

Table PlannedSchedule also contains non-unique columns PlannedDate and PlannedHours.

Suppose that there is only 1 entry in main and adv containing only 1 RequestID.

However, PlannedSchedule contains 3 entries (each with a different PlannedDate)for the same RequestID.

When a leftJoin() is used to join these 3 tables, instead of showing 3 entries for the 3 different PlannedDate. 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 GroupBy is required (this was tested on the SQL editor in PhpMyAdMin), however as there is no GroupBy is DataTables, the other way is to do the following:

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.

Which I wish to clarify for this particular example I am encountering.

I also wish to clarify, if I were to use a select distinct to 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.)