Need help with data query and display exercise

Hi everyone, I'm a newbie and I'm researching to do this exercise, hope it helps.

I have 3 entities

Project (id | name)
Group (id | name | project_id)
Staff (id | name | group_id | project_id)

Relationships

Project has many Group
Project has many Staff through Group (I think it solves the problem, but it doesn't seem to be any different from a regular relationship like hasMany)
Group belong to Project
Group has many Staff
Staff belong to Group

What i need to do

I want to print a page with some blocks like this: (i.imgur.com/vSYTXno.png)
Display all groups of project as separate blocks, in each block showing the staffs belonging to that block.
Each staff only belong to one Group.
If there are members who do not belong to any group (but in same project), they will be aggregated into a list and put in a separate block at the top of the "Group" blocks.

What I'm doing

Get Project and relationships based on project's id.
Query to Staff table to get which records with empty group_id and project_id same with step 1.
Passing results of 1 and 2 ($projects, $staffsNotInGroup) to view.
Use 2 loops to print list Group and Staff ($groups as $group and $group.staffs as $staff).
Loop $staffsNotInGroup on the top.

Questions

Did my steps make sense to solve this exercise? (Even though the results were displayed correctly).
Are there other more effective ways to solve this problem? (performance, queries, relationships, display,...)
Can I just use one query to do this or can I just separate them?

Thanks everyone for the help.

Sinnbeck

4 years ago

Level 102

Before answering I just want to clear one thing up. Why is a "staff" related to both a group and a project? If a group can only be related to one project, why not just have the staff related to the group only? Edit: ok so its because they can be outside any group but still part of the project?

Also please share the code you have currently for getting the data, so we have something to work from. (From "What I'm doing")

MinatoXX

4 years ago

Level 1

@Sinnbeck

All staff work on the project, and they divide into small groups to work. And there are some people who don't join the group.

What I wrote is based on what I assumed, I don't think they are the most optimal so hopefully can be suggested by everyone.

Sinnbeck

4 years ago

Level 102

@MinatoXX Ok. Can you show your actual code for What I'm doing ? Your logic sounds fine, but without code, it is hard to say for sure. I cannot say your queries are good/bad without seeing them

MinatoXX

4 years ago

Level 1

Here is the code that I am working on

Controller

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Project;
use App\Models\Staff;

class ExampleController extends Controller
{
	public function index()
	{
		$project = Project::where("id", 1)->with("groups")->first();
		$staffNotInGroup = Staff::whereNull("group_id")->where("project_id", $project->id)->get();

		return view('example', [
			'project' => $project,
			'staffNotInGroup' => $staffNotInGroup
		]);
	}
}

View

<h1>{{ $project->name }}</h1>

@if($staffNotInGroup)
<ul>
	@foreach ($staffNotInGroup as $staff)
	<li>
		{{ $staff->name }}
	</li>
	@endforeach
</ul>
@endif

<ul>
@foreach ($project->groups as $group)
    <li>
		{{ $group->name }}
		@if($group->staffs)
		<ul>
			@foreach ($group->staffs as $staff)
			<li>
				{{ $staff->name }}
			</li>
			@endforeach
		</ul>
		@endif
	</li>
@endforeach
<ul>

Sinnbeck

4 years ago

Level 102

@MinatoXX You need to load staff in those groups as well

$project = Project::where("id", 1)->with("groups.staff")->first(); //Load one level deeper

Other than that it should be ok. It should run 4 queries in total (for these queries). Check that it is indeed the case with debugbar or clockwork

Please or to participate in this conversation.