I have a relation Room. Each room has one owner which is a User entity, and each Room has multiple participants which are also User entity.
When the page loads, I query all rooms in React
once all the rooms are returned back, I query the database one by one with each rooms ID to get back the details of of the room stored in other relations (Room owner, room user count, room topic which is of entity Topic)
const fetchRooms = () => {
const csrf: HTMLMetaElement = document.querySelector(
"meta[name='csrf-token']"
) as HTMLMetaElement;
axios
.post(
`/room/all/${pageNumber}`,
{},
{
headers: {
"Content-type": "application/json",
"X-CSRF-TOKEN": csrf.content,
},
}
)
.then((response) => {
const data = response.data;
data.forEach((room: any) => {
//! TODO: merge each rooms details with its own data to avoid extra requests
axios
.post(
"/room/details",
{ id: room.id },
{
headers: {
"Content-type": "application/json",
"X-CSRF-TOKEN": csrf.content,
},
}
)
.then(({ data }) => {
if (data) {
setRooms((rooms) => [
...rooms,
<RoomCard
key={room.id}
id={room.id}
title={room.name}
img={null}
username={data.owner.username}
timestamp={room.created_at}
userCount={data.user_count}
topic={data.topic.name}
/>,
]);
}
});
});
})
.catch(({ response }) => {
console.log(response.status, response.data);
});
};
which laravel handles
// web.php
Route::controller(RoomController::class)->group(function () {
Route::post("room/all/{page:int}", "all")->name("rooms.all");
Route::post("room/details", "details")->name("rooms.details");
}
// RoomController.php
public function all(Request $request, $page = 1)
{
if (!Auth::check()) {
return response("", 403);
}
$total_posts = 20;
$rooms = Room::offset(($page - 1) * $total_posts)->limit($total_posts)->orderBy("id", "desc")->get();
return response()->json($rooms);
}
public function details(Request $request)
{
if ($request->id) {
$room = Room::find($request->id);
$user_count = RoomUser::where("room_id", $room->id)->count() 1;
return response()->json(["owner" => $room->owner, "user_count" => $user_count, "topic" => $room->topic]);
}
return response("", 403);
}
DB info
------------------------
| Tables_in_project |
------------------------
| failed_jobs |
| migrations |
| password_resets |
| personal_access_tokens |
| room_users |
| rooms |
| topics |
| users |
------------------------
Users
------------------- --------------------- ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
------------------- --------------------- ------ ----- --------- ----------------
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| username | varchar(255) | NO | UNI | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| email_verified_at | timestamp | YES | | NULL | |
| password | varchar(255) | NO | | NULL | |
| remember_token | varchar(100) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| profile_picture | varchar(255) | YES | | NULL | |
| privilege_level | int(11) | NO | | 0 | |
------------------- --------------------- ------ ----- --------- ----------------
Rooms
------------- --------------------- ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
------------- --------------------- ------ ----- --------- ----------------
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| description | text | YES | | NULL | |
| topic_id | bigint(20) unsigned | NO | MUL | NULL | |
| owner_id | bigint(20) unsigned | NO | MUL | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
------------- --------------------- ------ ----- --------- ----------------
Topics
------------ --------------------- ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
------------ --------------------- ------ ----- --------- ----------------
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| name | varchar(255) | NO | UNI | NULL | |
------------ --------------------- ------ ----- --------- ----------------
Users in rooms
------------ --------------------- ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
------------ --------------------- ------ ----- --------- ----------------
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| room_id | bigint(20) unsigned | NO | MUL | NULL | |
| user_id | bigint(20) unsigned | NO | MUL | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
------------ --------------------- ------ ----- --------- ----------------
The problem that I'm experiencing is that, this is very VERY inefficient, as I have to query the details of the room separately for EACH room, this is going to be very slow after its deployed, it's even slow in my local machine.
I'm unsure as how to deal with it.
The one route I'm aware of is using relations provided by Laravel but then there's another problem
When I query all rooms, I return them as it is back. The user model functions (the functions that access the one to one and one to many etc relations) are only available in Laravel, of course they are not sent back with the response to JS.
How can I query rooms table and include all the information relating the room (topic, owner, all users that are in the room; their IDs are stored in room_users, all users count)
I'm using:
- ReactJS 17.0.2
- Laravel 9
- PHP 8.1
- MySQL Ver 15.1 Distrib 10.7.3-MariaDB
CodePudding user response：
Thanks to aynber for helping in comments.
I solved it by using with function in eloquent.
In all function of RoomController I changed the query to
$rooms = Room::with(["topic", "owner", "users"])->offset(($page - 1) * $total_posts)->limit($total_posts)->orderBy("id", "desc")->get();
The array passed to with needs to have function names defined in model of Room which are as following in my case
public function topic()
{
return $this->belongsTo(Topic::class);
}
public function owner()
{
return $this->belongsTo(User::class);
}
public function users()
{
return $this->belongsToMany(User::class, "room_users");
}
CodePudding user response：
Should be able to just put ->with('users') on your eloquent model of rooms. Then you will have all users that belong to that room.