How to display a record from two tables based on current date

Hello, I'm creating an attendance system but i'm stuck. I have 3 tables: users, attendance_schedules and marks. users has: id, first_name, group_name, status etc columns. attendance_schedules has: group_name, meeting_date etc columns (this table stores information about upcoming meetings). marks has: user_id, meeting_date, status etc columns (this stores a marked attendance)

On the day of a meeting attendance_schedules->meeting_date, I want to display all the users that belong to the same group. However, when a user is marked, it should change the button from 'mark' to 'marked' (i am using marked->status to check if it is marked)

Display list of users base on their groups
Check if the user is marked for that Particular day
if the user is marked for that day (status = 1) then button show "Marked" else show "Mark"

Controller:

public function getUsersByMeetingGroup() {
    	$meeting_group = Request()->meeting_group; //from the views get url
    	$users = User::where('users.status', 1) // checks if user is active
				->where('users.meeting_group', $meeting_group)
                ->leftjoin('marks', 'marks.user_id', '=', 'users.id')
                ->select('users.id', 'users.first_name','users.first_name','users.meeting_group','users.batch', 'users.state_code','marks.status', 'marks.meeting_date', 'marks.arrival_time')
                ->get();
		return $users;
    }

Index:

@php $date = date("Y-m-d"); @endphp
@if($user->cds_date != $date)
    <button class="badge badge-primary border-0 p-2" type="submit" name="mark" value="mark">
    <i class="fa fa-check"></i>
      Mark
    </button>
@else
     <a class="badge badge-primary border-0 p-2">
      Marked
       <i class="fa fa-check-double"></i>
       </a>
 @endif

It still displays/duplicates users based on records of the user in marks table. OUTPUT

--------------------------------
Name | Group     | ********* | Action						
John   | Football | ********** | marked		// previous record in the database
John   | Football | ********** | mark										
-------------------------------

How do i solve this problem?

tisuchi

3 years ago

Level 70

@elexis May be you use where() condition in the closure in leftJoin to resolve the duplicate issue.

->leftJoin('marks', function ($join) use ($date) {
            $join->on('marks.user_id', '=', 'users.id')
                ->where('marks.meeting_date', $date);
        })

elexis

3 years ago

Level 1

@tisuchi $date is prompting an error

azimidev

3 years ago

Level 55

to me it looks like the issue is caused by the leftjoin in the query, which is resulting in multiple rows for the same user. You can try adding a condition to filter the query to only return the latest record for each user, based on the meeting_date field.

i modified your code as an example

public function getUsersByMeetingGroup() {
    $meeting_group = Request()->meeting_group;
    $users = User::where('users.status', 1)
                ->where('users.meeting_group', $meeting_group)
                ->leftjoin('marks', function($join) {
				// you needed this: 
                    $join->on('marks.user_id', '=', 'users.id')
                         ->where('marks.meeting_date', (DB::raw('(select max(meeting_date) from marks where marks.user_id = users.id)')));
                })
				// until here
                ->select('users.id', 'users.first_name','users.meeting_group','users.batch', 'users.state_code','marks.status', 'marks.meeting_date', 'marks.arrival_time')
                ->get();
    return $users;
}

elexis

3 years ago

Level 1

@azimidev Thank you for your answer. Your solution correctly removed the duplications, however, it is no longer displaying on the particular day of the meeting. It just displays without considering the date

azimidev

3 years ago

Level 55

@elexis ok then add another where clause in your left join

$users = User::where('users.status', 1)
                ->where('users.meeting_group', $meeting_group)
                ->leftjoin('marks', function($join) {
                    $join->on('marks.user_id', '=', 'users.id')
                         ->where('marks.meeting_date', (DB::raw('(select max(meeting_date) from marks where marks.user_id = users.id)')))
                         ->where('marks.meeting_date', Request()->meeting_date);
                })
                ->select('users.id', 'users.first_name','users.meeting_group','users.batch', 'users.state_code','marks.status', 'marks.meeting_date', 'marks.arrival_time')
                ->get();

elexis

3 years ago

Level 1

@azimidev hello,

i have tried it but it returns the marks columns as "Null". so there is no way to check if the user is marked (marks.status = null)
It displays other meeting groups that don't have their meeting on the day. However, it doesn't mark them. it throws an exception when mark button is clicked.

I'm thinking of a way to link the 3 tables: attendance_schedules(stores meeting day & meeting group name), users, and marks(stores marked attendance). Such that it can only display users based on their meeting groups and the date of the meeting (From the attendance schedules). Then if the user has been marked (marks table) the status should be 1 (which i can use to change the state of the button)

azimidev

3 years ago

Level 55

@elexis you could add a "group_name" column to the "users" table, which would link to the "attendance_schedules" table's "meeting_group_name". And then, you could add a "user_id" column to the "marks" table, which would link to the "users" table's "id".

When you want to display users based on the meeting group and date, you can use a SQL query to join the "attendance_schedules", "users", and "marks" tables on their common columns, and filter the results based on the desired meeting group and date.

Then, you can use the information from the "marks" table to determine the attendance status for each user and display it accordingly. If the status is 1, you can change the state of the button.

Please or to participate in this conversation.