Level 102
Show your code for getting the data currently. Is it for an api or just blade?
Aug 10, 2022
10
Level 1
Show limited information in relation table in laravel and my sql
I have three tables, a table called date, a table of users, and a table to create a connection for the table of users and the date named userdate. For example, 5 users have registered in today's date. I want the table to show only the date with the number of users, and the next line shows the next date with the number of users. my database like this.... date_table {id,date} users_table{id,name,...} user_date_table{user_id,date_id}
Level 1
@Sinnbeck no this is in controller and blade,is not api
Level 102
@shayan_sahrayi so it's just to limit the request to the database? In blade you decide what is shown. Code?
Level 27
If it’s just the date a user registered, why do you have your table structure set up as a many-to-many relationship with a pivot table?
Also, if this is a new project where you’re free to create and modify the tables as you wish (and not an existing database already used in a production site), why not use standard Laravel names? That will save you a lot of bother when defining models and relationships.
If you use a standard Laravel scheme, you only need the users table, and then you can simply aggregate by the created_at and count the rows.
1 like
Level 1
@kokoshneta No, this is a website for online appointments. Doctors book the doctor's date according to the patient's date
Level 27
@shayan_sahrayi Oh I see – so you don’t mean ‘registered’ as in ‘registered their user account’, but as in ‘registered an appointment with the doctor’.
I still don’t see why you need a table for dates, though, or why it needs to be many-to-many. If it’s about users registering appointments, you need a users table and an appointments table (assuming each appointment only has one user) and relationships between the two. For example:
SQL table structure
+---------------+
| Table 'users' |
+---------------+
| id |
| name |
| ... |
| created_at |
| updated_at |
+---------------+
+-------------------------+
| Table 'appointments' |
+-------------------------+
| id |
| user_id |
| doctor_id |
| time // datetime column |
| ... |
| created_at |
| updated_at |
+-------------------------+
Eloquent models (relationships only)
class User extends Model {
public function appointments() {
return $this->hasMany(Appointment::class);
}
}
class Appointment extends Model {
public function user() {
return $this->belongsTo(User::class);
}
}
To show how many appointments you have per day over the next week, for example, you could then do something like this:
$start = now()->startOfDay();
$end = now()->addWeek()->endOfDay();
$appointmentDates = Appointment::whereDate('time', '>=', $start)
->whereDate('time', '<=', $end)
->orderBy('time ASC')
->get()
->groupBy(fn($appointment) => $appointment->time->toDateString());
That will give you a collection of appointments grouped by dates; the number of appointments in a given day is then easy to output in a table in a Blade file, for instance:
<table>
<tbody>
@foreach ($appointmentDates as $date => $appointments)
<tr>
<td>{{ $date }}</td>
<td>{{ $appointments->count() }}</td>
</tr>
@endforeach
</tbody>
</table>
1 like
Level 1
@kokoshneta no this not true,The dates are already registered in the management panel, and these dates are displayed to the user, and by selecting the date, the user registers on that date.
Level 122
@shayan_sahrayi so actually your dates table is like appointments. Seems an odd way to go about it
From your original question you just want a count of entries for a specific date?
like
2022-08-08 4
2022-08-09 6
2022-08-10 3
Level 1
@Snapey Yes sure
Level 1
@Snapey please see this table
table appointments or meet date id ............ date......... created_at.... updated_at....
table users id.... name...... phonenumber..........
table appointments_users appointments_id..... user_id.....
Please or to participate in this conversation.