2

I have a custom module which has all the admin users listed. Now I want to list admin users with specific roles.

In my grid collection code it has below code which get all the users:

$collection = $this->_userFactory->create()->getCollection();
    $table = $collection->getTable('authorization_role');

    $collection->getSelect()
    ->join(['role' => $table], 'main_table.user_id=role.user_id');
    $collection->addFieldToFilter('role.role_name', ['nin' => ['Administrators', 'XYZ']]);

    $this->setCollection($collection);

    parent::_prepareCollection();

    return $this;

I want to show all users which do not have these 2 roles "Administrator, XYZ".

How could I make join in the collection?

Thanks!

4

I got the solution as below:

use  Magento\User\Model\ResourceModel\User\CollectionFactory as UserCollectionFactory;

    /**
    * @var \Arrow\Customerassignment\Model\userFactory
    */
    protected $_userFactory;

    public function __construct(UserCollectionFactory $userFactory)
    {
        $this->_userFactory = $userFactory;
    }

    /**
     * @return $this
     */
    protected function _prepareCollection()
    {
        $adminUsers = $this->_userFactory->create();

        $excludedUsersId = [];

        foreach($adminUsers as $adminUser) {
            if(in_array($adminUser->getRole()->getRoleName(), ['Administrators', 'XYZ'])) {
                $excludedUsersId[] = $adminUser->getId();
            }
        }

        $collection = $adminUsers->addFieldToFilter('main_table.user_id', ['nin' => $excludedUsersId]);

        $this->setCollection($collection);

        parent::_prepareCollection();

        return $this;
    }


| improve this answer | |
0

I just send you the example of join query

You can see the code and try to make it your own query using this format.

$collection = $productCollection->create()->addAttributeToSelect('entity_id');

$collection->getSelect()->join(
array('value_entity' => $collection->getTable('catalog_product_entity_media_gallery_value')),
'e.entity_id = value_entity.entity_id',
array('value_id'));


$collection->getSelect()
->reset(\Zend_Db_Select::COLUMNS)
->columns('entity_id')        
->group(array('entity_id'));

echo '<pre>';print_r($collection->getData());die('died');
| improve this answer | |
0

Try below code it will join role table with user table and gives you both table data and filter based on role name.

    $collection = $this->_userFactory->create()->getCollection();
    $table = $collection->getTable('authorization_role');
    $connection = $collection->getConnection();
    $collection->getSelect()
        ->join(['role' => $table], 'main_table.user_id=role.user_id');
    $collection->addFieldToFilter('role.role_name', ['nin' => ['Administrators', 'admin']]);`

UPDATE

    $collection = $this->_userFactory->create()->getCollection();
    $table = $collection->getTable('authorization_role');
    $connection = $collection->getConnection();
    $collection->getSelect()
        ->join(['role' => $table], 'main_table.user_id=role.user_id', ['role_user_id' => 'role.user_id']);
    $collection->addFieldToFilter('role.role_name', ['nin' => ['Administrators', 'admin']]);
| improve this answer | |
  • showing error below: 2 exception(s): Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_id' in order clause is ambiguous, query was: SELECT main_table.*, role.* FROM admin_user AS main_table INNER JOIN authorization_role AS role ON main_table.user_id=role.user_id WHERE (role.role_name NOT IN('Administrators', 'RegionalManager')) ORDER BY user_id DESC LIMIT 20 – Utsav Gupta Feb 4 '19 at 12:49
  • update your full code. – Rakesh Varma Feb 4 '19 at 12:51
  • question updated please check and let me know where i am wrong. – Utsav Gupta Feb 4 '19 at 13:02
  • try updated answer – Rakesh Varma Feb 4 '19 at 13:06
  • it showing all the user, join condition not working. :( – Utsav Gupta Feb 4 '19 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.