How to add a count field from another mysql table when using the php Editor

MrHarry

Hello,
I have the table 'dealerships' with various fields. I would also like to have a calculated field called total_devices,
which is a count of all records in the table devices where the field dealership_id = dealerships.id.

I have something like the below code in mind but I do not know how to have access to the dealerships.id.
I have written XXXXX where I do not know how to access the current dealerships.id in the query.
Thank you in advance.

Editor::inst( $db, 'dealerships' )
->fields(
Field::inst( 'id' )->set(false), // ID is automatically set by the database on create
Field::inst( 'name' ),
Field::inst( 'address' ),
Field::inst( '(SELECT count(*) FROM devices dev WHERE dev.dealership_id = XXXXX)', 'total_devices' )
->set(false)

)
})
->debug(true)
->process( $_POST )
->json();

allan

What I would suggest doing, is to use an Mjoin. Then on the client-side you can just display the length of the array - e.g. data: 'devices.length'.

Allan

MrHarry

Thank you very much for your answer Allan. Greatly appreciated.
I figured it out by myself after resting a while.
This is the line of code that served my purpose:

Field::inst( '(SELECT count(*) FROM devices WHERE devices.dealership_id = dealerships.id)', 'total_devices' )
      ->set(false)

allan

Nice one. Thanks for posting your solution.

Allan