Question 15442 in Laravel Question posted in
The official Laravel Documentation can be found here.

Attendance get classes whose attendance not submitted – Laravel

Creating Attendance system in laravel where need to check the classes for which attendance is submitted and which classes pending. Students data is saved in students table.

At the time of attendance the class whose attendance is submitted are saved in attendence_dates table. (To check which day for which class is working and data final submitted or not)

public function up()
{
    Schema::create('attendence_dates', function (Blueprint $table) {                  
        $table->bigIncrements('id');
        $table->date('date')->default(date("Y-m-d"));
        $table->string('clas_name', 20)->nullable();
        $table->string('section_name',20)->nullable();
        $table->boolean('working')->default(false);
        $table->boolean('final')->default(false);
        $table->string('remarks', 100)->nullable();
        $table->unsignedBigInteger('updated_by')
          ->nullable(); 
        $table->timestamp('updated_at')
           ->useCurrent()->useCurrentOnUpdate();
    });
}

And the Attendances table where the attendance of students are saved with P AND A status.

   public function up()
    {
        Schema::create('attendences', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->integer('att_month')->default('0')->nullable();
            $table->date('att_date')->useCurrent();            
            $table->string('clas_name', 20)->nullable();
            $table->string('section_name',20)->nullable();                           
            $table->unsignedBigInteger('student_id')->nullable();
            $table->unsignedBigInteger('acc_id')->nullable();
            $table->enum('status', ['P', 'A'])->default('P');                
            $table->foreign('student_id')->references('id')->on('students')
                    ->onDelete('SET NULL')
                    ->onUpdate('CASCADE'); // SET NULL, CASCADE, RESTRICT, DO NOTHING, NO ACTION
            $table->foreign('acc_id')->references('acc_id')->on('students')
                    ->onDelete('SET NULL')
                    ->onUpdate('CASCADE');                                         
            $table->timestamps();
        });
    }

used this code to fetch all existing classes and sections from ‘students’ table.

$classAll   = DB::table('students')
    ->select('clas_name', 'section_name')
    ->groupBy('clas_name','section_name')
    ->orderBy('clas_name','ASC')
    ->orderBy('section_name','ASC')
    ->get();

And this to fetch classes and sections whose attendance is submitted.

// FETCH POSTED CLASSES DATA
$posted = DB::table('attendence_dates')
        ->select('clas_name', 'section_name')
        ->where('date', $date)
        ->groupBy('clas_name','section_name')
        ->orderBy('clas_name','ASC')
        ->orderBy('section_name','ASC')
        ->get();

Now have to show the pending classes i.e. classes which not submitted.
I am learning laravel, from search and documentation i got this collection function.
tried $pending = $classAll->diff($posted); but its giving error "Object of class stdClass could not be converted to string ".

Also tried $pending = $classAll->diffKeys($posted);
its not giving any error, but not getting desired results. Total count is reduced but not exact classes removed from total.

I want to get the classes and sections which attendance not submitted on particular date. If there is any function available in laravel or query to get the desired result.

*** As asked for further explanation, Class And Sections table included in question
P.S. I want to further explain that why i am fetching classes from students table instead of class and section table. There may be some empty classes in class table and some classes may have 5 (A,B,C,D,E) sections but some may have 2(A,B) or 3 sections. So I am saving class names and sections in students table & directly loading classes from students table.

Class Table

public function up()
{
    Schema::create('class', function (Blueprint $table) {
        $table->Increments('id');
        $table->string('name', 50);
        $table->string('full_name', 50);
        $table->boolean('status')->default(1);
        $table->timestamps();        
    });
}

Sections Table

 public function up()

    {
        Schema::create('sections', function (Blueprint $table) {
            $table->integer('id',true,true);
            $table->string('name', 50);
            $table->string('short_name', 50); 
            $table->boolean('status')->default(1);
            $table->timestamps();
        });
    }
Tags:

2

Answers


  1. 0

    I think you can run a query to get all the names of the classes that are not present in a certain date in the attendance_date table.
    It could be like

    SELECT *
FROM students
WHERE clas_name NOT IN (
    SELECT clas_name
    FROM attendance_date
    WHERE date = <your date>
);
    Login or Signup to reply.
  2. 0

    Since you’re not interested in doing it properly at the database layer, just use array_udiff() to compare the two arrays. I am not very familiar with Laravel, so I am assuming that $classAll and $posted are arrays of objects:

    $pending = array_udiff($classAll, $posted, function($a, $b) {
    if (
        $a->clas_name === $b->clas_name &&
        $a->section_name === $b->section_name
    ) {
        return 0;
    } else {
        return -1;
    }
});

    I am leaving my previous answer here as it still shows a better approach to the scenario.

    As you have not included the classes and sections tables in your question, this assumes that you have:

    • classes (id, name)
    • sections (id, name, class_id)

    One way to get the classes/sections with attendance not submitted for a given date:

    SELECT
    c.name AS clas_name,
    s.name AS section_name
FROM classes c
JOIN sections s ON c.id = s.class_id
WHERE NOT EXISTS (
        SELECT 1
        FROM attendance_dates
        WHERE clas_name = c.name
        AND section_name = s.name
        AND date = :specific_date
)
ORDER BY clas_name, section_name;

    With Laravel’s Query Builder it should be something like this:

    $classesNotSubmitted = DB::table('classes c')
    ->join('sections s', 'c.id', '=', 's.class_id')
    ->select('c.name clas_name', 's.name section_name')
    ->whereNotExists(function($query) {
            $query->selectRaw(1)
                  ->from('attendance_dates')
                  ->whereRaw('clas_name = c.name')
                  ->whereRaw('section_name = s.name')
                  ->where('date', $date)
        })
    ->orderBy('clas_name', 'ASC')
    ->orderBy('section_name', 'ASC')
    ->get();

    If you want to get the full list of classes/sections and whether attendance was submitted for a given date you could move the subquery into the SELECT list:

    SELECT
    c.name AS clas_name,
    s.name AS section_name,
    EXISTS (
        SELECT 1
        FROM attendance_dates
        WHERE clas_name = c.name
        AND section_name = s.name
        AND date = :specific_date
    ) AS att_taken
FROM classes c
JOIN sections s ON c.id = s.class_id
ORDER BY clas_name, section_name;

    With Laravel’s Query Builder:

    $classesAll = DB::table('classes c')
    ->join('sections s', 'c.id', '=', 's.class_id')
    ->selectRaw('c.name clas_name, s.name section_name, EXISTS (
                                        SELECT 1
                                        FROM attendance_dates
                                        WHERE clas_name = c.name
                                        AND section_name = s.name
                                        AND date = ?) AS att_taken', [$date])
    ->orderBy('class_name', 'ASC')
    ->orderBy('section_name', 'ASC')
    ->get();

    I am not a Laravel user and these have not been tested, so may need tweaking to get them to work.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search