Dropdown. Display value from a 3rd linked table.

Erik Skov

New DTE project. 3 tables. X, user_type

table 1: users: id, lastName, FirstName

table 2: usersOfType. This contains details for a specific type of user that only pertain to a certain type of user.
id, user_id which ties to users table.

table 3: actionsByUsersOfType. Many to 1 of this table to table 2.
id, usersOfType_id.

DTE is CRUD of table 3. For display, names show just fine. For EDIT, Need to display names of users in drop down where the value is usersOfType.id

Editor::inst($db, 'actionsByUsersOfType', 'id')
        ->fields(

// I can do this, and it works

//            Field::inst('actionsByUsersOfType.usersOfType_id')
//            ->options(Options::inst()
//                    ->table('usersOfType')
//                    ->value('id')
//                    ->label(['code'])
//                    ->render(function ( $row ) {
//                            return $row['code'];
//                    })
//            )
//            ->validator('Validate::dbValues'),

I want to do something like this, but I cannot figure out where to put the references to the 3rd table, users.

            Field::inst('actionsByUsersOfType.usersOfType_id')
            ->options(Options::inst()
                ->table('usersOfType, users')
                ->value('usersOfType.id')
                ->label(['users.LAST_NAME', 'users.FIRST_NAME'])
                ->render(function ( $row ) {
                    return  $row['users.LAST_NAME'] . ', ' . $row['users.FIRST_NAME'];
                })
               ->where('users.id', 'usersOfType.user_id', '=', false)
            )
            ->validator('Validate::dbValues'),

            Field::inst('users.FIRST_NAME'),
            Field::inst('users.LAST_NAME')

...

        ->leftJoin('usersOfType', 'usersOfType.id', '=', 'actionsByUsersOfType.usersOfType_id')
        ->leftJoin('users', 'users.id', '=', 'usersOfType.user_id')

allan

Hi,

The Options class does not currently have a join option, so what you are looking for would need to be accomplished by either:

1. A custom function that will read the db with a join, or
2. Create a VIEW that does the join and have the Options class read form that.

Allan

Erik Skov

Allan, thank you for your answer. We had come up with the VIEW option. I would like to know more about custom functions. Where can I find examples?

colin

Hi @Erik Skov ,

If you look in /lib/Editor/Options.php you'll see the supplied functions. Allan meant to add an extra one in there to do what you want.

Cheers,

Colin

allan

The docs also discuss it if you want a higher level view of it.

Allan

Erik Skov

Could I use a join Mjoin to achieve this?
found this https://datatables.net/forums/discussion/48521 and thought it might apply.

allan

No - I'd suggest against using MJoin for this (which is why I didn't mention it above). The issue with using Mjoin is that it expects a link table (or "junction table" if you prefer) with just references and no other data. It will delete and add rows as required, so any data in other columns would be lost!

A VIEW is the best way of doing it at the moment I'd say. If you don't have db access to create a view then use a custom query.

Allan