'Custom' computed column in Editor - PHP
'Custom' computed column in Editor - PHP
TooManyTables
Posts: 23Questions: 7Answers: 1
Assume I have a table called Equipment, which holds various pieces of information about specific items (equipmentID, Name, Description, Brand etc.) and a Users table with a userID and all of the usual data there. Certain users are trained to use certain pieces of equipment, so there is an EquipmentUsers table which contains basically two important pieces of data: equipmentID and userID.
I would like to show an editable list of all of the Equipment, with a non-editable column containing an indication of whether the currently logged-in user has been trained on it (i.e. has an equipmentID/userID combo on EquipmentUsers). I've seen questions about computed columns before, but they seem to be dealing with computing on the client-side (jQuery). In this case, though, I believe that I need to perform it on the server-side (PHP) - basically creating a 'custom' column that doesn't exist in the database, then send that through to be displayed like normal. It'd be a subquery in normal SQL, but I'm not sure DT Editor will let me do such a thing, because the resulting column isn't actually on the table.
Am I barking up the wrong tree? If so, what should I actually be doing to solve this problem? If not, how does such a thing work in Editor?
PHP
$userID = $siteUser->_userID;
Editor::inst($db, 'Equipment', 'Equipment.equipmentID' )
->field(
Field::inst( 'Equipment.equipmentID' ),
Field::inst( 'Equipment.equipmentAssetNumber' ),
Field::inst( 'Equipment.equipmentName' ),
Field::inst( 'Equipment.equipmentBrand' ),
Field::inst( 'Equipment.equipmentModel' ),
Field::inst( 'Equipment.equipmentArea' ),
Field::inst( 'Campus.campusID' ),
Field::inst( 'Building.buildingName' ),
Field::inst( 'Room.roomNumber' ),
Field::inst( 'Equipment.equipmentDoorCode' ),
Field::inst( 'Equipment.equipmentStatus' )
##Calculated field would go here##
)
->leftJoin( 'Room', 'Equipment.equipmentDoorCode', '=', 'Room.doorCode' )
->leftJoin( 'Building', 'Room.buildingID', '=', 'Building.buildingID' )
->leftJoin( 'Campus', 'Building.campusID', '=', 'Campus.campusID' )
->leftJoin( 'EquipmentUser', 'Equipment.equipmentID', '=', 'EquipmentUser.equipmentID' )
->process($_POST)
->json();
This question has accepted answers - jump to:
Answers
Hi,
The PHP libraries for Editor 1.7 introduced the ability to execute SQL functions and other statements such as sub-selects for readonly columns. Here is a trivial example:
The first parameter is the SQL "field" (in this case a sub-select doing a basic calculation), and the second parameter is the name that should be used for the property in the JSON data (you might use
Equipment.calculationor something).Regards,
Allan
Cool,thanks @allan that looks to be what I'm after. Followup question, how do I bind a parameter (:userID) to said query? I've tried:
and
But both fail with an error stating that the respective methods (DataTables\Editor\Field::bind() and DataTables\Editor\Field::where()) are undefined.
Good question - there isn't currently a bind method for the field to allow that. The underlying query has a
bindmethod (which you can access in awhereclause) so you could try:it is a bit of a hack and I've not tried it locally yet, but that will hopefully work.
Allan
Hm, the query runs, but I'm getting null for Equipment.canUse regardless of the actual count. Using the debug method to get the actual query being run and then plugging that into SQL Server gives me an actual result (i.e. 0 or 1). I still get null when doing the request, even if I swap out the count and ask it to just return something arbitrary. And not just null in the 'nothing's being displayed' sense, I'm looking at the DT object the server is returning and canUse is literally null.
PHP
That results in the following query, according to the almighty debugger:
Though for the purposes of an MVE, that can be reduced to:
In the
Database/Query.phpfile you'll find a line:if you change it to be:
I believe it will then work. If you were to use an "as" alias in your sub-select it would need a slightly bigger update to the libraries, which I've got locally and will ship in 1.7.3.
Allan
Happy to report that that did the trick, in conjunction with another change. I had to make sure I had the full table.field syntax everywhere. For example:
(SELECT count(userID) FROM EquipmentUser WHERE EquipmentUser.equipmentID = Equipment.equipmentID AND userID = :userID)would still return null, but
(SELECT count(eu.userID) FROM EquipmentUser eu WHERE eu.equipmentID = Equipment.equipmentID AND eu.userID = :userID)would work. For anyone referring to this thread in future, keep in mind that there's a 128 character limit on field names, which a query apparently counts as, hence the table alias 'eu'.
Thank you for your stellar assistance, allan. The time and effort you put into both DataTables and helping out lost souls like myself is above-and-beyond.
Final code: