Dropdown. Display value from a 3rd linked table.

Dropdown. Display value from a 3rd linked table.

Erik SkovErik Skov Posts: 33Questions: 6Answers: 2

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')

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    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 SkovErik Skov Posts: 33Questions: 6Answers: 2

    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?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

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

    Allan

  • Erik SkovErik Skov Posts: 33Questions: 6Answers: 2

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

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin
    Answer ✓

    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

This discussion has been closed.