Group By | Relationship Many To Many Laravel 5

I am developing an application in Laravel 5 where I have the tables employees,companies, exams andemployee_exams and their respective fields as outlined below:

Table companies (social_reason, cnpj, ...)
Table employees (name, office, company_id, ...)
Table exams (exam, code, periodical, ...)
Table PIVO employee_exams (employee_id, exam_id, last_exam, next_exam)

In the pivo employee_exams table I store the data of the next exams of each employee.

What I need now is to list the exams that are about to expire in a range that will be defined in search filters with the following structure:

1 - COMPANY 
    1 - Employee 
        1 - Exam 
        2 - Exam 
        3 - Exam 

    2 - Employee 
        1 - Exam 
        2 - Exam 

    3 - Employee 
        1 - Exam 
        2 - Exam 
        3 - Exam 
  
    
2 - COMPANY 
    1 - Employee 
        1 - Exam 
        2 - Exam 

    2 - Employee 
        1 - Exam 
        2 - Exam 
        3 - Exam

My question is how to perform the search with Eloquent or Query Builder and later display the data in an html table.

I did something like:


    DB :: table ('employee_exams')
            -> join ('exams', 'employee_exams_exam_id', '=', 'exams.id')
            -> join ('employees', 'employee_exams_employee_id', '=', 'employees.id')
            -> join ('companies', 'companies.employee_id', '=', 'employees.id')
            -> groupBy ('companies_id')
            -> get ();

Unsuccessfully. I need help with the above problem.

jlrdw

8 years ago

Level 75

Make 1 - COMPANY a header with next company link

Write a custom paginator whereby you paginate employee information.

When that company is at end, have your next company link paginate to next company, like

https://drive.google.com/file/d/0B1_PFw--3o74YVliNHAxbzd4Z2c/view?usp=sharing

This was just a quick example, not formatted or prettied up as a quick demo of a double paginator.

Top header is owner, with paginated pets. Most in quick example had no pets, again quick example.

You could also write a triple paginator if needed. Of course if this stuff is just a short list, you may not even need any pagination.

In that case, just use the working query (if your example above works) and getPdo();

And / or see

http://dsmithweb.com/itemized.png

and

http://laravel.io/forum/04-04-2015-looping-through-collection

and

https://laracasts.com/discuss/channels/laravel/complex-query-for-a-report

and remember most complex reporting is NOT active record, but like a single page app. Just like I showed here.

You have to ALLOW and take no data and nulls into account. Checking for null in your results.

Another solution is having extra columns in the tables to "cut down" on the many to many problem. Sometimes it's better to have 3 one to many worked than a many to many mess. Read: http://laravel.io/forum/05-12-2015-has-many-through-relationship-depth

ouhare

8 years ago

Level 24

Hm, if all your relationships are well configured, I suppose you can do :

$result = Company::with(['employees', 'employees.exams' => function ($q) {
    $q->where('expired_at', '<', $anyDate);
}])->get();

not sure about edger loading syntax..

Please or to participate in this conversation.