[Captain Paralytic]

Here is an interesting one I have just come across. Any assistance in solving it would be great.

I have a table of Staff. Let's call the sample set:

Jim
Scotty,
Spock
Bones

Now, each of them is able to take on certain roles.
Jim => producer, editor
Scotty => editor, assistant
Spock => researcher
Bones => editor

Next, I have a table of Shoots. This table includes columns with positions: producer, editor, assistant, researcher. A member of staff may perform more than one roll at a shoot, but no member of staff may be assigned to a position unless they have that role in their profile (note, there is also a status column in the staff table which must show available for the staff member to be considered at all).

Now ensuring that only available staff appear in the list for say "producer" is easy: $crud->set_relation('producer','staff','name', 'status < 3');
And adding multiple roles to a member of staff is easy: $crud->set_relation_n_n('roles', 'staff_roles', 'roles', 'staff_id', 'role_id', 'role');

But how to ensure that the list of available staff for the producer field contains only Jim (in this example).


I hope this explanation is clear.

[Captain Paralytic]

Well I figured one way to solve it, thus:
Instead of:
[color=#282828][font=helvetica, arial, sans-serif]

$crud->set_relation('producer','staff','name', 'status < 3');

[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]I used:[/font][/color]

$query = $this->db->query("

SELECT

staff.staff_id,

staff.name

FROM `staff`

JOIN staff_roles USING ( staff_id )

WHERE staff.status < 3 AND staff_roles.role_id = 2");

$data = $query->result();

foreach ($data as $d)

$producers[$d->staff_id] = $d->name;