PHP Laravel how to sort by using two tables

Question

I have two MySQL tables in my Laravel-application, one called categories and the other called employees. The structure of the categories-table is:

id
category
order

and the employees table also has columns called:

id
category
order

So, lets say I have categories like: Consultants, Programmers and Administration and when I create an Employee in the backend I can assign the new employee to one of these categories. Now in the frontend of my Laravel-application I want the Employees displayed by the categories, and also the categories by order they are given. Let's say Consultants has order of 2, Programmers order of 1 and Administration order of 3.

Right now my controller looks like this:

use App\Employee;

class EmployeesController extends Controller
{

    public function index()
    {
       $employees = Employee::all()->groupBy('category');

       return view('app.employee.index', compact('employees'));
    }
}

and my blade view file:

@foreach ($employees as $category => $workers)
  <div class="col text-center mb-6">
    <h2>{{ $category }}</h2>
  </div>
  <div class="row px-4 px-xl-10">
    @foreach($workers->sortBy('order') as $worker)
      // content of the employee
    @endforeach
 </div>
@endforeach

This sorts the employees correctly by simply using the categories of the Employees-table but with this I'm not able to sort by categories like I want to as described above.

So, can someone help me out?

EDIT

As an example I want the output look like this:

Programmers (since this category has order of 1)
 // employees with category "programmers" here

Consultants (2)
 // employees with category "consultants" here

Administration (3)
 // employees with category "administration" here

Not very clear to me. Can you show an example of input and expected output? — mighty_dev
– mighty_dev, Commented May 6, 2019 at 8:16
So the order used for sorting Employees is from Employee table or categories table? — mighty_dev
– mighty_dev, Commented May 6, 2019 at 8:29

henrik · Accepted Answer · 2019-05-06 08:43:03Z

1

To me you column definitions are a bit confusing, may I suggest a change to your columns:

Table 'categories'
------------------
id
name
order

Table 'employees'
-----------------
id
category_id

Add a foreign key to the employees table:

$table->foreign('category_id')->references('id')->on('categories')

And then your models could be mapped to each other with relationship methods:

class Employee extends Model
{
    public function category()
    { 
        return $this->belongsTo(Category::class);
    }
}

class Category extends Model
{
    public function employees()
    { 
        return $this->hasMany(Employee::class);
    }
}

So with this in place we can simply query the database from the controller by:

use App\Category;

class EmployeesController extends Controller
{

    public function index()
    {
       $categories = Category::orderBy('order')->get();

       return view('app.employee.index', compact('categories'));
    }
}

and display the results in your blade view:

@foreach ($categories as $category)
  <div class="col text-center mb-6">
    <h2>{{ $category->name }}</h2>
  </div>
  <div class="row px-4 px-xl-10">
    @foreach($category->employees as $employee)
      // content of the employee
    @endforeach
 </div>
@endforeach

edited May 6, 2019 at 8:43

answered May 6, 2019 at 8:30

henrik

1,6483 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ST80 Over a year ago

oh ok, so when I get this right I got to add a Category model too?

henrik Over a year ago

Yes, it helps a lot when using Laravel to also use the built in eloquent support in models for your data querying

ST80 Over a year ago

hmm when I try to do $table->foreign('category_id')->references('id')->on('categories') I get an error:

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails....CONSTRAINT 'employees_category_id_foreign' FOREIGN KEY ('category_id') REFERENCES 'categories' ('id')

:-/

henrik Over a year ago

Thats because youre trying to add a foreign key to a table rhat hasnt been created yet. Change order of migration files or create all your foreign keys in last migration file

Mosi · Accepted Answer · 2019-05-06 08:29:54Z

0

I think you must change your query to :

$categories = Category::with(['employees'=>function($q){
   $q->orderBy('order');
}])->orderBy('order')->get();

answered May 6, 2019 at 8:29

Mosi

1986 bronze badges

Collectives™ on Stack Overflow

PHP Laravel how to sort by using two tables

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related