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

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

MrHarryMrHarry Posts: 2Questions: 1Answers: 1

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();

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin

    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

  • MrHarryMrHarry Posts: 2Questions: 1Answers: 1
    Answer ✓

    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)
    
  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin

    Nice one. Thanks for posting your solution.

    Allan

Sign In or Register to comment.