So I have this crm project where each user has specific area acceess eg. certain state access. I have splited that data to 4 different tables including users table where other 3 cotains user id as foreign key. Simple hasMany relation, but to avoid stroring of large data for each each I do not stored no data in district table which is the 4th table if the user has all state access.
table are in follwoing order
users user_zone user_state user_district
For example if user id 1 has 1 zone access, 1 full state access then data will be stored in
user_zone as user_id = 1 and zone_id = 1 single row and same in user_state single row but user_district do not have any row.
in second scenario user id 2 has 1 zone access, 1 full state access then data will be stored in
user_zone as user_id = 2 and zone_id = 1 single row and same in user_state and user_district.
Problem: While searching from data with user has specific state and district I don't get user 1 is list while it's valid. I have added one macro like below
Builder::macro('user_area_filter', function ($parameters) {
return $this->distinct('users.id')
->with('get_district', 'get_state')
->get()
->map(function ($user) {
if ($user->get_district->count() == 0) {
$user->get_district = \Districts::whereIn('state_id', $user->get_state->pluck('state_id'))->get();
}
return $user;
})
->map(function ($user) use ($parameters) {
$user->get_states_count = $user->get_state->whereIn('state_id', $parameters['states'])->count();
$user->get_districts_count = $user->get_district->whereIn('district_id', $parameters['districts'])->count();
$user->get_state = $user->get_state->toArray();
$user->get_district = $user->get_district->toArray();
$user->valid = 0;
if ($user->get_states_count > 0 && $user->get_districts_count > 0) {
$user->valid = 1;
$columns = array_merge($parameters['columns'], ['valid', 'get_state', 'get_district']);
return $user->only($columns);
}
})->where('valid', 1);
});
->with('get_district', 'get_state')
//these are hasMany relation from Users model
\Districts
//is the master district table
//full query
$query = User::where('is_active', 1)->select('id', 'name', 'official_mobile', 'email', 'profile_id', 'is_active', 'app_version', 'last_login')->whereHas('get_user_data', function ($q) {
$q->where('work_on', 1);
})
->user_area_filter([ //macro
'columns' => ['id', 'name', 'official_mobile', 'email', 'profile_id', 'is_active', 'app_version', 'last_login'],
'states' => $states,
'districts' => $districts
]);
$totCount = $query->count();
$models = $query->take(200)->skip(0);
above code just works but I am not sure how it'll perform in long run. Here is a snapshot of DB schema https://nimb.ws/iWTBKX
