Custom complex read only field

bplanet

Hi, I really need some help. I am stuck.

I have three tables joined together:

interventions
interventions_units
units

What I want to achive is, to create two custom fields units.LastLocation and units.CurrentLocation. For each row I want that units.ID is inserted on both queries. How can I do that?

Code is bellow.

Best regards,
Žiga

$Units = Editor::inst($GLOBALS["db"], 'units', "units.ID")->fields(         
        
        Field::inst('units.Title'),
            
        // ------------------------ //      
        // --- READ ONLY COLUMN --- //
        // ------------------------ //  
        Field::inst('(SELECT CONCAT(interventions.Street, interventions.StreetNumber, interventions.Municipality) '
                    . 'FROM interventions '
                    . 'LEFT JOIN interventions_units ON interventions.ID = interventions_units.InterventionID '
                    . 'WHERE interventions.Status = \'finished\' AND interventions_units.UnitID = **:unitID** '
                    . 'ORDER BY interventions.DateStart DESC '
                    . 'LIMIT 1)', 'units.LastLocation'),
        
        // ------------------------ //      
        // --- READ ONLY COLUMN --- //
        // ------------------------ //
        Field::inst('(SELECT CONCAT(interventions.Street, interventions.StreetNumber, interventions.Municipality) '
                    . 'FROM interventions '
                    . 'LEFT JOIN interventions_units ON interventions.ID = interventions_units.InterventionID '
                    . 'WHERE interventions.Status = \'active\' AND interventions_units.UnitID = **:unitID** '
                    . 'ORDER BY interventions.DateStart DESC '
                    . 'LIMIT 1)', 'units.CurrentLocation')

allan

So the condition would change per row that is being selected? That is not something that is currently possible in Editor's libraries. I'm not actually even sure how you would do that in plain SQL... A sub-select perhaps.

Allan

bplanet

Hi Allen,

thank you for a quick reply. What do I need to change that I will get two new read-only fields which select fields from other tables (in this example concat strings from table interventions)?

allan

I read the SQL more than I read the question yesterday - sorry. Let's step back for a moment:

What I want to achive is, to create two custom fields units.LastLocation and units.CurrentLocation. For each row I want that units.ID is inserted on both queries. How can I do that?

So when you do an update to units, you want to write into those two columns? Your best option would be to use a trigger. ON INSERT OR UPDATE ...

Alternatively, use a server-side event to write into the database.

Regards,
Allan