0

I have a complaints table, which stores all complaints a user register, and these complaints are assigned to some users to solve them.

I don't have any relational table which maps which complaint is assigned to which user, indeed i have a table employees_records which stores information of all users to whom some complaints are assigned. (since these codes are on a live server, we cant change the db structure for now)

For now we get which complaint is assigned to which user is via

$v=Complaints::find($id);
$dUser = EmployeesRecord::whereJsonContains('categories', "$v->category_id")
                ->whereJsonContains('sub_categories', "$v->code_id")
                ->whereJsonContains('zones', "$v->zone_id")
                ->whereJsonContains('wards', "$v->ward_id")
                ->whereJsonContains('cities', "$v->city_id");
                ->where('department_id',$v->department_id)->first();

if $dUser is empty, it means complaint is not assigned to any user and if we get a user, it means complaint is assigned to that user.

At the time of complaint registration, we get categories,sub_categories, zones, wards, cities,department_id data. Which is checked against that any user is under that IDs.

FINALLY THE PROBLEM :

I want to get all complaints which are not assigned to any user. But the process to know whether the complaint is assigned or not to a user (as I have mentioned above) is complicated to me. And I am not able to map this thing in a query. I want a query with high performance since I am using this in a report generation.

What i tried.

I tried to get all the complaints first 

    $complaints=Complaints::all();
$assigned_complaints=[];
$un_assigned_complaints=[];
foreach($complaints as $k => $v)
{
    $dUser = EmployeesRecord::whereJsonContains('categories', "$v->category_id")
                ->whereJsonContains('sub_categories', "$v->code_id")
                ->whereJsonContains('zones', "$v->zone_id")
                ->whereJsonContains('wards', "$v->ward_id")
                ->whereJsonContains('cities', "$v->city_id");
                ->where('department_id',$v->department_id)->first();
    if($dUser)
    {
        $assigned_complaints[]=$k;
    }
    else
    {
        $un_assigned_complaints[]=$k;
    }
}
$total_assigned_complaints=count($assigned_complaints);
$total_un_assigned_complaints=count($un_assigned_complaints);

But i want a solution, which applies on the Complaint model query not with looping the Complaints data. As the data is very large in number.

Vishal Srivastava
  • 542
  • 2
  • 5
  • 19
  • Break the query into smaller steps and see where does it break and then act upon it... start with dd `EmployeesRecord::whereJsonContains('categories', "$v->category_id")->get()` – lewis4u Jun 15 '20 at 14:16
  • @lewis4u : my query is not breaking i am getting the result, but it is taking long time to execute, because of looping entire complaints table data. I want to get all unassigned complaints without first getting all complaints and then looping then and applying the logic. – Vishal Srivastava Jun 15 '20 at 14:23
  • @VishalSrivastava Why use json columns if you are planning to run where conditions rigorously? Second, you need to take a look at optimizing your code. I suggest going through https://stackoverflow.com/a/60015685/5326191 for a quick head start. – Digvijay Jun 16 '20 at 03:51
  • @Digvijay: Thanks for the reply, but as I wrote, these DB tables are not made by me, these are already existing so I can't change them. – Vishal Srivastava Jun 16 '20 at 05:40

0 Answers0