CakePHP 3 - Query Matching Condition depending on joinTable result data

832 views Asked by At

I'm trying to create a query that return companies based on a joinTable result

I have 3 tables: Companies, Units and Users

Companies belongsTo Units
Units hasMany Companies

Users belongsToMany Units - ['joinTable' => 'users_units']
Users belongsToMany Companies, ['joinTable' => 'users_companies']

Units belongsToMany Users - ['joinTable' => 'users_units']);

Companies belongToMany Users

JoinTables:

UsersUnits belongsTo Users - ['joinTable' => 'users_companies']
UsersUnits belongsTo Units

UsersCompanies belongsTo Users
UsersCompanies belongsTo Companies

I want to get all companies depending on two conditions:

One condition is:

UsersCompanies has both user_id and company_id

I've achieved this by using:

$query = $this->find()->orderAsc('Companies.name');
if ($userId)
    $query->matching(
            'Users',
            function ($q) use ($userId)
            {
                return $q->where(['Users.id' => $userId]);
            }
        );
$query->matching('Units');

Now I want to explain another condition

UsersUnits table has one column named 'admin', which means that the player related to this record, is a unit admin.

When I get the company record, I receive a unit_id.

I want to check if the player is an admin of companies' unit

Depending on it, I want to get all companies of that Unit, otherwise, I want to get only the companies related to player

For example:

Companies->find('all')

Get unit_id

unit_id -> Units->matching('Users',
    function ($q) use ($userId)
    {
        return $q->where(['Users.id' => $userId]);
    }
)

if (UsersUnits.admin == 1)
    Units->contain('Companies')
else
    Companies->matching('Units')

I hope you guys understand

If isn't that clear, please let me know


PS.: Just to let you guys know

For now I have this solution:

// In Companies Model
$query1 = $this
    ->Units
    ->find('list',
        [
            'keyField' => 'name',
            'valueField' => 'id'
        ]
    )
    ->matching(
        'Users',
        function ($q) use ($userId)
        {
            return $q
                ->where(['Users.id' => $userId])
                ->where(['UsersUnits.admin' => true]);
        }
    );

$query1Array = $query1->toArray();

$query2 = $this
    ->find('list',
        [
            'keyField' => 'name',
            'valueField' => 'id'
        ]
    )
    ->matching('Units')
    ->orderAsc('Companies.name');

if(count($query1Array) > 0)
    $query2->where(['Units.id IN' => $query1Array]);

$query3 =
    $this->find('list',
        [
            'keyField' => 'name',
            'valueField' => 'id'
        ]
    )
    ->matching('Users',
        function ($q) use ($userId, $query1Array)
        {
            return $q
                ->where(['Users.id' => $userId]);
        }
    );

$result = array_merge($query2->toArray(), $query3->toArray());

Is there a better way to achieve this?

0

There are 0 answers