Include sub-selects in table view mode (not the editor view)

lifestyle

How can I do the following (or CAN I do the following) to show a count of related records (something I can easily do in regular datatables with ajax server-side data). I am not sure how it can be accomplished with the editor server-side syntax...

select col1, col2, col3, (select count(*) from table2 where fk.joinfield = table1.id) as numSubRecords from table1

I never want to update or modify that count, I just want to see it on a per row basis like I used to be able to do with just plain datatables (local or serverside).

Help?

allan

Good question - there actually isn't a way of doing this with the Editor PHP libraries at the moment. I want to add non-editable 'get' function and sub-query options to Editor in future, but at the moment what you would need to do is run that query manually (with the sql method) when getting the data, but use the Editor libraries (without the sub-query part) to handle the insert, edit and delete actions.

Allan

lifestyle

Fair enough, can you provide a simple generic sample code of how to do this for my benefit and other reading this?

allan

if ( ! isset( $_POST['action'] ) ) {
   $data = $db->sql( 'SELECT id as DT_RowId, field1, field2, ... FROM ... WHERE ... ' )->fetchAll();
   echo json_encode( array(
      'data' => $data
   ) );
}
else {
   ... editor stuff
}

I think should do it.

Allan