Summany data - Count / Sum / Average etc

TPCSLtd

Using Editor and PHP backend.

If I have 2 related tables, a header record with a one-to-many relationship with a detail record, what would the nicest way be to summarise the detail records.

For example I have a header table containing a site visit record and a detail table linked by a FK listing each action related to that site visit.

If I wanted to display a datatable containing the site visit header data with a count of the number of linked records, is this best achieved using MJOIN and counting the size of the JSON object returned when the table is rendered?

To try and explain, Before Editor, with just Datatables, I would have got my data from the db with something along these lines:

SELECT COUNT(D.id) as N, H.ref, H.blah, H.blah2 FROM Visit H LEFT JOIN Detail D on D.id=H.hid

TPCSLtd

For anyone looking at this in the future, I managed to count data in a linked table using Mjoin and counting the size of the JSON object client side.

The Mjoin looks like

Editor::inst( $db, 'tblAddress' )
    ->fields(
        Field::inst( 'tblAddress.Company' )->validator( 'Validate::notEmpty' ),
        //etc
    )

    ->join(
        Mjoin::inst('tblAppointment')
            ->link('tblAddress.id', 'tblAppointment.AddressID')
            ->fields( Field::inst('id'))  //just grab the id's so we have something to count
        )
    ->process( $_POST )
    ->json();

Then client side:

...
columns: [
         \\items snipped for clarity
            { data: "visitcount", render: function (data, type, row) { return  Object.keys(row.tblAppointment).length;} },
    \\etc..
]

Possibly not overly efficient in terms of bandwidth for large data sets, but seems to behave OK for me.

I'm thinking getFormatter is probably more efficient, assuming you have an unused field you can use to pass the value to the client.

HTH

allan

Hi,

Thanks for posting back with your solution. Apologies for missing your original post.

The solution you found is currently really the only way of doing this with Editor, and is the approach that I've used in a few applications myself. Indeed, you can see it in use on the many join upload example.

This workaround is required as Editor's SQL modeling currently doesn't support either SQL functions or the GROUP BY clause which is typically needed for aggregation.

I hope to add that ability in future, but at the moment, the approach you've taken is spot on.

Regards,
Allan