filter databasejoin from list records

[prophoto]

I have 3 lists

Teachers
Students
Classes

In the classes list I will create joins to both teachers and students lists. I'd like to filter the joins so that each teacher only has one record in the classes table. Ideas? I don't have anything setup yet, been racking my brain for a way but can't come up with it. Thanks!

 

[prophoto]

This doesn't give me a filter, but at least it will disable options with matching rows.

http://fabrikar.com/forums/index.php?threads/eval-options-in-dbjoin.44967/

This goes in the element Details > Advanced > Eval Options

$value = $opt->value; //gets Teacher's row ID from dropdown list
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select('teacher_name') //teacher_name is the name of the dbjoin in the classes table
    ->from('jos_classes'); //classes table
$db->setQuery($query);
$column= $db->loadColumn();

if (in_array($value, $column, true)) {
$opt->disable = true;
}

 

[cheesegrits]

I think a WHERE filter of something like ...

WHERE {thistable}.id NOT IN (SELECT teacher_name FROM #__classes WHERE id != '{rowid}')

The subquery will select all teacher_name already in use in the classes table, except the one for "this" row. Then the outer part says "only use ids that aren't in the ones already being used".

Of course, this only restricts the options on the dropdown to ones not in use, it doesn't actually enforce uniqueness in any real database constraint sense. But should prevent teachers being assigned to more than one class on the form.

You might also want to apply an "isunique" validation, for the case where two people load the form at the same time, then submit it with the same teacher on different classes.

-- hugh