Restricting via a lookup table

Sometimes you have a lookup table, and the lookup table contains something you want to restrict by. For example, you might have a list of the interest groups under which a faculty member needs to be listed under. Your tables might be:

• faculty
• groups
• faculty_groups

The “faculty_groups” table has two fields in it (besides its own ID field): faculty_ID and group_ID. If you want to list all of the faculty in the “Student Careers” group, you could join groups to faculty_groups and then faculty_groups to faculty and limit by “Student Careers”. But most of the time when you’re doing this, you don’t need to limit that way. You know the ID for the group you want, and you’re not displaying anything from that table. You’re displaying faculty.

The “restrictByLookup” method handles that for you. In this example, if you’re displaying faculty you make your SQL connection to that database. Then you restrict by lookup. Assume here that the group ID is 5:

$db->restrictByLookup('faculty_groups', 5);

This will display only those faculty who are listed as part of the group with ID 5.

Nonstandard ID fields

If your ID fields aren’t named in the way the plug-in expects (current table, singular, and ”ID”), you can specify the ID field as a third parameter. For example, if your lookup ID field were “personID” and your group ID field were “organizationID”, you would use:

$groupRestriction = array('organizationID'=>5); $db->restrictByLookup('faculty_groups', $groupRestriction, 'personID');

The plugin would then match the ID field of the current table (faculty) to the field “personID” in faculty_groups.