many to many options for editable field

wowzaaa

Hi

I have the following use case:

users table: it contains users related data but not role_id
roles table: it contains roles related data
role_user pivot table: it contains role_id and user_id ... it is used to let users have multiple roles

then i have a Datatable with an Editor with a column Publisher
i need in that column to display the Publisher name and when i click on it i need a select with all publishers in the db: so i need to get all users with the publisher role from the db

this is the field setup i am trying to adapt:

Field::inst( 'items.publisher_id' )->options(
Options::inst()
->table( 'users' )
->value( 'id' )
->label( 'name' )
)->validator( 'Validate::dbValues', array( 'valid' => array('') ) ),

i literally have no idea how to adapt it

thanks

rf1234

Please take a look at this example: https://editor.datatables.net/examples/advanced/joinLinkTable.html

The options instance that is relevant for you is the second one, not the first one which is about a direct link through a foreign key between "users" and "sites".

Field::inst( 'user_dept.dept_id' )
            ->options( Options::inst()
                ->table( 'dept' )
                ->value( 'id' )
                ->label( 'name' )
            ),

This example is also about a link table. You have table "users", link table "user_dept" and the second table "dept". I have attached the E/R diagram of the example.

The options instance populates the link table with department ids by having the user selecting department names. Since table "users" is the primary table of the Editor instance the user id for the link table is already there.

Hope this helps you.

allan

Assuming that you know the SQL query that you want to execute to get the options, the easiest option is going to be to create a VIEW for that query, and then use the Options class to read from that view.

What is the query that you would use to read the options?

Allan