How to apply where statement on multi-valued data returned by Mjoin ?

lfabry

Is there a way to filter selected data based on the multi-valued attributes of a Mjoin ?

Error messages shown: An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'roles.id_account' in 'where clause'
Description of problem:
My problem is the following :
I have a user tables, a role table, a N-N table in between 'users_roles'. The table role has an id account for 1-N relationship with the account table. In this scope account denote an organisation. A user is part of one or multiple accounts depending on the role he has that are part of an account.
I am trying to create an editor on my users. So I work on my table user, I use an Mjoin to get the array of roles this user posesses. But then I need to filter the user based on permissions.
Then I have a list of permitted accounts. I am trying to add a where clause to my editor in order to fetch only users that have at least 1 role with an id_account attribute that is part of the $permittedAccounts. In the where statement I use permittedAccountsSQL which is a string of accounts id such as "('1', '3')" to match with in statement.

I tried different variations but the recurring issue is that 'roles.id_account' is not accessible outside of the scope of the Mjoin->fields. Hence I have a "Column not found error"

        $thisEditor = Editor::inst($this->editorDb, 'user')
            ->fields(
                Field::inst('user.id')->set(false),
                Field::inst('user.username')->validator('Validate::notEmpty'),
                Field::inst('user.realname'),
                Field::inst('user.description'),
            );
        $thisEditor->join(
            Mjoin::inst('roles')
                ->link('user.id', 'users_roles.id_user')
                ->link('roles.id', 'users_roles.id_role')
                ->order('id desc')
                ->fields(
                    Field::inst('id')->options(Options::inst()
                        ->table('roles')
                        ->value('id')
                        ->label('title')
                    ),
                    Field::inst('roles.title'),
                    Field::inst('roles.description'),
                    Field::inst('roles.id_account')
                        ->options(Options::inst()
                            ->table('account')
                            ->value('id')
                            ->label(array('company_name', 'id'))
                            ->render(function ($row) {
                                return $row['company_name'] . ' (' . $row['id'] . ')';
                            })
                            ->order('id DESC')
                        ),
                )
        );
        $thisEditor->where(function ($q) use ($permittedAccountsSQL) {
            $q->where('roles.id_account', $permittedAccountsSQL, 'IN', false);
        });
        $thisEditor->process($post)->json();

allan

Is there a way to filter selected data based on the multi-valued attributes of a Mjoin ?

I presume you mean you want to knock out top level rows which do not match the condition on the Mjoin? In which case, no, sorry. There is no easy way of doing that.

The issue is that the Mjoin is a second query. We make our main query to get the parent rows of the table, and then a second one to get the Mjoin information. You can apply a condition to the Mjoin, but it would just be applied to that second query.

The only way around this is to create a VIEW that would perform the Mjoin action for you and you can then query that VIEW and apply whatever condition is required to that.

Allan

lfabry

Thank you very much for your quick response.
This is exactly what I meant.
I am working on a workaround at the js level to post filter the data.