Populating a form's select elements when related entities are historical, and normally hidden

Questions Components & MVC
,
1

This isn’t a show-stopper, but rather a question of deciding on the best way to handle it.

I’ll borrow the old Bug Tracker example, the entity-relationship scenario that’s used in the Doctrine documentation (and I believe has also been in the ZF docs).

Suppose your Bugs thing has been around for a long time and there are hundreds of Engineers, some of them no longer with the hypothetical company. Say we’ve given them a boolean “active” property which we set to false when they leave because (assume they have a data history) we can’t delete them without offending referential integrity, right? But we normally don’t want to display them (e.g., in a SELECT element) when, for example, our Chief Executive Micromanager is using our Bug form to create a new Bug and choose somebody to assign, because the “inactive” Engineers are not available. So you populate your Engineers select element with a database query that says “SELECT … WHERE active.” Ah, but if they load a form that’s bound to what might call a historical Bug entity, whose assigned Engineers have departed long since… you see the problem. The right option element won’t be found in the SELECT unless we do something more to put it there.

I can think of a couple of approaches. One would be to change whatever method you’re using to fetch the data for the SELECT so that it takes the entity id as an optional parameter and, if provided with this entity id, does some SQL kung-fu along the lines of

SELECT DISTINCT e.id, e.lastname FROM engineers e 
LEFT JOIN bug_engineers be ON e.engineer_id = be.engineer_id 
LEFT JOIN bugs b ON b.id = be.bug_id   
WHERE active or (e.id = be.engineer_id AND be.bug_id = ?)

or, as in my case, its DQL equivalent since I’m using Doctrine.

Another approach would be to do something closer to the rendering of the form. You could even do it in a viewscript by snagging the entity with $form->getObject() and looking for properties that point to entities that are “inactive.” Upon finding one, you get the appropriate id and human-readable label out of the entity and shove it into the SELECT element’s array of value options.

I think both have pros and cons. The first approach seems like a lot of contortion for something that is not usually going to be happening, but it would certainly settle the issue. Rather than changing the database access, I kind of like the idea of inspecting the entity and burning the extra data I’m going to need into the viewscript one way or another, possibly with extra element attributes data-inactive_whatever_id=xxx and data-inactive_whatever_label=“Foobar” along with some javascript. (I’m thinking of this because there are going to be cases where I’ll be re-populating some SELECTs via xhr, and will need a way to “remember” the extra work that has to be done after the form is initially rendered.)

Any thoughts?

2

I’d go with the first option. The active / inactive stuff is a database detail: the rest of the application shouldn’t have to worry about it. If you’re doing xhr stuff, just pass the bugId to your rest service and let that take care of it.

Incidentally you may want to use a UNION instead of an OR in your query. For many databases they’re much faster. Don’t know much about Doctrine’s support tho.

3

yeah you’re probably right about encapsulating the inactive|active stuff further down the pipe.

not sure how I would apply UNION to this, but I have no doubt Doctrine provides a way to do it or a way to make it do it :slight_smile:

Thanks.

4

just an update in case anyone is ever curious. I ended up following the above suggestion, tweaking the DQL in my Doctrine repository methods so that they would say

 public function getPersonOptions($hat_id, $person_id = null)
{
    $dql = "SELECT DISTINCT p.id AS value, CONCAT(p.lastname, ', ', p.firstname) AS label "
        . 'FROM MyProject\Entity\Person p JOIN p.hat h '
        . 'WHERE (h.id = :hat_id AND p.active = true)';
    if ($person_id) {
        $dql .= " OR p.id = $person_id";
    }
    // etc
}