Three way belongsToMany relation ship in laravel Eloquent

What does the score mean? Is it a number of tasks completed by a group? Does the task table have a 'completed' column or something similar?

yes. its the total number of a particular task completed by that group. and as per the above example, it will be for Task 1 currently there is no completed column. as I am still confused about how to make the tables and what to do with this.

Can one task belong to many groups? Thegroup_task table suggests that. Maybe that's a case but I don't think so. In my opinion, it would be best to delete the group_task table and add completed column to the tasks table. Then you can make a simple query like this:

select
    tasks.group_id, groups.name, count(*) as completed_tasks
from tasks
join groups
on groups.id = tasks.group_id
where tasks.completed = 1
group by tasks.group_id

Of course you can use Eloquent for that.

yes groups can have multiple tasks which can be same also. that's the reason i am in this dilema XD

Okay, my bad :) Tell me, what does the task table contain? Can one group complete the task with id 7 and at the same time another group may not have it completed? If it's true, how do you track completed tasks? If it's not true (completed task mean completed for every group), then this query should help:

select
    groups.group_id, groups.name, count(*) as completed_tasks
from group_task
join groups
on groups.id =  group_task.group_id
join tasks
on tasks.id =  group_task.task_id
where tasks.completed = 1
group by groups.group_id

That is what I was confused about. should I make a task_user table and pass group_id as a third parameter. or is there a better way to do that. a user can perform the same task over and over if they prefer and we need to keep track of the count for that task by every user. the overall scenaior is going to give the total score for the group for that task.

e.g in group 1

• user 1 can do task 1 for 3 times.
• user 2 can do task 1 for 1 time. so the group 1 score is 4.

in group 2

• user 3 can do task 1 for 0 times.
• user 4 can do task 1 for 1 time. so the group 2 score is 1.

But the user can have multiple groups, right? It would be easier if you provided the current database schema :)

Theoretically, no, user can have multiple groups. but I had made it so that practically they can be assigned to multiple groups.

In terms of Schema, it's our normal users and tasks table structure is as below:

     Schema::create('tasks', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('group_id');
        $table->string('title');
        $table->text('description');
        $table->string('start_date');
        $table->string('end_date');
        $table->integer('status_id')->default(2);
        $table->bigInteger('creator_id');
        $table->bigInteger('moderator_id');
        $table->timestamps();
        $table->softDeletes();
    });

for the groups table:

    Schema::create('groups', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->text('description');
        $table->integer('status_id')->default(2);
        $table->bigInteger('creator_id');
        $table->bigInteger('moderator_id');
        $table->timestamps();
        $table->softDeletes();
    });

I was thinking that if a table can store group sessions against the task and then we can call a count for every task that completed, givens use the end result. but how to do with pivot table and get the count also against the task id is what i am stuck with.