CODEWITHSUNDEEP
mysqldatabaselaraveldatabase-designlaravel-5
Vladimir Djukic
Vladimir Djukic

Reputation: 2072

3 relation how database should look and how to create queries wtih Laravel Eloquent?

I have 3 relations:

project can have many tasks and tast have one project

user can have many tasks and task can have many users

user can have many projects and project can have many users --> this relation I already have in pivot table project_user

How other tables should look like and how can I query them to take all tasks for current logged user and some project id ?

Upvotes: 1

Views: 36

Answers (1)

Mateo Barahona
Mateo Barahona

Reputation: 1391

Table task should have "foreign key" project_id. Then you can find all tasks associated to one project.

You should have a table for linking users and tasks, like user_task with two fields : user_id/task_id

You already have project_user for the last part.

To query them : To get all project tasks

SELECT * FROM task WHERE project_id = {project_id}

Get all tasks for a user :

SELECT DISTINCT task.*
FROM user_task
INNER JOIN task ON task.id = user_task.task_id
WHERE user_task.user_id = {user_id}

Get all tasks for user and project id

SELECT DISTINCT task.*
    FROM user_task
    INNER JOIN task ON (task.id = user_task.task_id AND project_id = {project_id})
    WHERE user_task.user_id = {user_id}

With Eloquent :

DB::table('user_task')
            ->join('task', function($join)
        {
            $join->on('task.id', '=', 'user_task.task_id')->where('project_id', '=', {project_id})
        })
->where('user_task.user_id', '=', {user_id})
       ->get();

Upvotes: 1

Related Questions