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?