[TechDaddies-Kevin]

I have 3 tables:

Events
id|title|owner_id

Users
id|email address

Users_Groups
group_id|user_id


On my events CRUD form, I need the dropdown to show users (This works great) but I need the list to only show users who belong to group_id 1 in the Users_Groups table.

How would I accomplish this? I believe we need to use the set_model method, but I'm having a difficult time understanding the documentation in that regard.

[joshyro]

I think this can be done with database relations, look this example http://www.grocerycrud.com/examples/set_a_relation

[TechDaddies-Kevin]

We are already using the set relation method. The problem is that we need to filter the list of related items by using a third table.

[Kobus Myburgh]

Hi, just throwing an idea out to you - I have not tested this but maybe this helps.

From the documentation it seems you can not add a table, but it should be possible to use tablename.fieldname in a $crud->where() call.

If that works, it may be a hint to update the documentation for that?

Regards,

Kobus

[TechDaddies-Kevin]

That almost works, but not quite. When I use the where clause in my set_relation call, the following query is run:

SELECT * FROM (`users`) WHERE `users_groups`.`group_id` = 1 AND users_groups.user_id = users.id

This would work perfectly if I could change that FROM `users` to be FROM `users`, `users_groups`

[dropcase]

Are you looking for something like a join?

/topic/264-join-tables/#entry1018

[Kobus]

Kevin,

Did you manage? You could also set a relation, that will automatically create the correct join, when in conjuntion with the $crud->where() clause, you can probably do what you wanted to.

Please let us know?