'Custom' computed column in Editor - PHP

'Custom' computed column in Editor - PHP

TooManyTablesTooManyTables 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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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:

    Field::inst( '(SELECT age*2)', 'multiple' )
    

    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.calculation or something).

    Regards,
    Allan

  • TooManyTablesTooManyTables Posts: 23Questions: 7Answers: 1
    edited February 2018

    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:

    Field::inst( 'SELECT count(userD) FROM EquipmentUser WHERE EquipmentUser.equipmentID = Equipment.equipmentID AND userID = :userID', 'Equipment.canUse' )
                ->bind(':userID', $userID) 
    
    

    and

    Field::inst( 'SELECT count(traineeID) FROM EquipmentUser', 'Equipment.isTrained' ) 
        ->where(function($q) use ($userID) {
             $q->where('EquipmentUser.equipmentID', 'Equipment.equipmentID', '=');
             $q->where('EquipmentUser.userID', ':userID',  '=' );
             $q->bind(':userID', $userID);                
    })
    

    But both fail with an error stating that the respective methods (DataTables\Editor\Field::bind() and DataTables\Editor\Field::where()) are undefined.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    Good question - there isn't currently a bind method for the field to allow that. The underlying query has a bind method (which you can access in a where clause) so you could try:

    ->field(
      Field::inst( 'SELECT count(userD) FROM EquipmentUser WHERE EquipmentUser.equipmentID = Equipment.equipmentID AND userID = :userID', 'Equipment.canUse' )
    )
    ->where(function($q) use ($userID) {
             $q->bind(':userID', $userID);               
    })
    

    it is a bit of a hack and I've not tried it locally yet, but that will hopefully work.

    Allan

  • TooManyTablesTooManyTables Posts: 23Questions: 7Answers: 1
    edited February 2018

    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

    $userID = $siteUser->_userID;
        
        Editor::inst($db, 'Equipment', 'Equipment.equipmentID' )
            ->debug(true)
            ->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.equipmentNumberAvailable' ),
                    Field::inst( 'Equipment.equipmentInductionRequired' ),
                    Field::inst( 'Equipment.equipmentCanUseInSitu' ),
                    Field::inst( 'Equipment.equipmentBookable' ),
                    Field::inst( 'Equipment.equipmentBookerGroup' ),
                    Field::inst( 'Equipment.equipmentBookingMinimum' ),
                    Field::inst( 'Equipment.equipmentBookingMaximum' ),
                Field::inst( 'Equipment.equipmentStatus' ),
                Field::inst( '(SELECT count(traineeID) FROM EquipmentUser WHERE EquipmentUser.equipmentID = Equipment.equipmentID AND userID = :userID)', 'Equipment.canUse' )
                )
        ->leftJoin( 'Room', 'Equipment.equipmentDoorCode', '=', 'Room.doorCode' )
        ->leftJoin( 'Building', 'Room.buildingID', '=', 'Building.buildingID' )
        ->leftJoin( 'Campus', 'Building.campusID', '=', 'Campus.campusID' )
        ->leftJoin( 'EquipmentUser', 'Equipment.equipmentID', '=', 'EquipmentUser.equipmentID' )
        
        ->where(function($q) use ($userID) {
            $q->where('Equipment.equipmentBookable', true, '=');
            $q->where('Equipment.equipmentStatus', 'Disposed Of', '!=');
            $q->bind(':userID', $userID);
        })
        ->process($_POST)
        ->json();
    

    That results in the following query, according to the almighty debugger:

    SELECT  [Equipment].[equipmentID] as 'Equipment.equipmentID', 
    Equipment].[equipmentAssetNumber] as 'Equipment.equipmentAssetNumber', 
    [Equipment].[equipmentName] as 'Equipment.equipmentName', 
    [Equipment].[equipmentBrand] as 'Equipment.equipmentBrand', 
    [Equipment].[equipmentModel] as 'Equipment.equipmentModel', 
    [Equipment].[equipmentArea] as 'Equipment.equipmentArea', 
    [Campus].[campusID] as 'Campus.campusID', 
    [Building].[buildingName] as 'Building.buildingName', 
    [Room].[roomNumber] as 'Room.roomNumber', 
    [Equipment].[equipmentDoorCode] as 'Equipment.equipmentDoorCode', 
    [Equipment].[equipmentNumberAvailable] as 'Equipment.equipmentNumberAvailable', 
    [Equipment].[equipmentInductionRequired] as 'Equipment.equipmentInductionRequired', 
    [Equipment].[equipmentCanUseInSitu] as 'Equipment.equipmentCanUseInSitu', 
    [Equipment].[equipmentBookable] as 'Equipment.equipmentBookable', 
    [Equipment.[equipmentBookerGroup] as 'Equipment.equipmentBookerGroup', 
    [Equipment].[equipmentBookingMinimum] as 'Equipment.equipmentBookingMinimum', 
    [Equipment].[equipmentBookingMaximum] as 'Equipment.equipmentBookingMaximum', 
    [Equipment].[equipmentStatus] as 'Equipment.equipmentStatus', 
    (SELECT userID FROM EquipmentUser WHERE EquipmentUser.equipmentID = Equipment.equipmentID AND userID = :userID) 
    FROM  [Equipment] LEFT JOIN [Room] ON [Equipment].[equipmentDoorCode] = [Room].[doorCode]  
    LEFT JOIN [Building] ON [Room].[buildingID] = [Building].[buildingID]  
    LEFT JOIN [Campus] ON [Building].[campusID] = [Campus].[campusID]  
    LEFT JOIN [EquipmentUser] ON [Equipment].[equipmentID] = [EquipmentUser].[equipmentID] 
    WHERE [Equipment].[equipmentBookable] = :where_0 
    AND [Equipment].[equipmentStatus] != :where_1 
    

    Though for the purposes of an MVE, that can be reduced to:

    $userID = $siteUser->_userID;
        
        Editor::inst($db, 'Equipment', 'Equipment.equipmentID' )
            ->debug(true)
            ->field(
                Field::inst( 'Equipment.equipmentID' ),
                Field::inst( '(SELECT count(userID) FROM EquipmentUser WHERE EquipmentUser.equipmentID = Equipment.equipmentID AND userID = :userID)', 'Equipment.canUse' )
                )
        ->leftJoin( 'EquipmentUser', 'Equipment.equipmentID', '=', 'EquipmentUser.equipmentID' )
        
        ->where(function($q) use ($userID) {
            $q->bind(':userID', $userID);
        })
        ->process($_POST)
        ->json();
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    In the Database/Query.php file you'll find a line:

    if ( $addAlias && $field !== '*' && strpos($field, '(') === false ) {
    

    if you change it to be:

    if ( $addAlias && $field !== '*' ) {
    

    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

  • TooManyTablesTooManyTables Posts: 23Questions: 7Answers: 1
    edited February 2018

    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:

    $userID = $siteUser->_userID;
        
        Editor::inst($db, 'Equipment', 'Equipment.equipmentID' )
            ->debug(true)
            ->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.equipmentNumberAvailable' ),
                    Field::inst( 'Equipment.equipmentInductionRequired' ),
                    Field::inst( 'Equipment.equipmentCanUseInSitu' ),
                    Field::inst( 'Equipment.equipmentBookable' ),
                    Field::inst( 'Equipment.equipmentBookerGroup' ),
                    Field::inst( 'Equipment.equipmentBookingMinimum' ),
                    Field::inst( 'Equipment.equipmentBookingMaximum' ),
                Field::inst( 'Equipment.equipmentStatus' ),
                Field::inst( '(SELECT count(eu.userID) FROM EquipmentUser eu WHERE eu.equipmentID = Equipment.equipmentID AND eu.userID = :userID)', 'Equipment.canUse' )
                )
        ->leftJoin( 'Room', 'Equipment.equipmentDoorCode', '=', 'Room.doorCode' )
        ->leftJoin( 'Building', 'Room.buildingID', '=', 'Building.buildingID' )
        ->leftJoin( 'Campus', 'Building.campusID', '=', 'Campus.campusID' )
        ->leftJoin( 'EquipmentUser', 'Equipment.equipmentID', '=', 'EquipmentUser.equipmentID' )
        
        ->where(function($q) use ($userID) {
            $q->where('Equipment.equipmentBookable', true, '=');
            $q->where('Equipment.equipmentStatus', 'Disposed Of', '!=');
            $q->bind(':userID', $userID);
        })
        ->process($_POST)
        ->json();
    
    
    
This discussion has been closed.