Join two tables with multiple ids

Published 1 month ago by mehulkoradiya

i am trying to join table like tasks.employee_ids = ["3","4","5"] = employee_id , Tasks::join('project','tasks.project_id','=','project.id')->join('employee','tasks.employee_id','=','employee.id')->sortable()->paginate(5); but table not join please help me

Best Answer (As Selected By mehulkoradiya)
Yorki

Try this

Tasks::join('project','tasks.project_id','=','project.id')
    ->join('employee', function ($join) {
        $join->on(DB::raw('find_in_set(tasks.employee_ids, employee.id)'), DB::raw(''), DB::raw(''));
    })
    ->sortable()
    ->paginate(5); 
ben.nxumalo

what exactly are out trying to do?

Can it be done using model relations eg $this->hasMany(Task::class);

?

mehulkoradiya

yes

mehulkoradiya

i am trying to join tasks and employee table and display number of employee work on this tasks

Balawant

refere laravel documentations joins .....

Balawant

clearly explain both tables ....contents ...it is very easy ....

mehulkoradiya

ID Project Title Assign To Due Date Status 3 Bug Fixes task 1 emp1, emp2, emp3 29-06-2018 active

display employee's (emp1, emp2 ,emp3) work on task Bug Fix.

Yorki
Yorki
1 month ago (29,510 XP)

What is this you just pasted? If you want to get help you have to clarify your table schemas and relations between them. Not pasting unreadable pieces of html. Show me your migrations files to table employee, project and tasks.

mehulkoradiya

join 'tasks' table and 'employee' table using join() for example Tasks::join('project','tasks.project_id','=','project.id')->join('employee','tasks.employee_ids','=','employee.id')->sortable()->paginate(5);

employee_ids = ["2","3","5"]

Yorki
Yorki
1 month ago (29,510 XP)

Is this many to many relation? You should use relationship's joining table then.

tasks
    - id
    - project_id

project
    - id

employee
    - id

tasks_employee
    - tasks_id
    - employee_id
mehulkoradiya

yes, -> Tasks model

public function project()
{
    return $this->belongsTo(Project::class, 'project_id');
}

public function employee()
{
    return $this->belongsTo(Employee::class, 'employee_id');
}

-> Project model

public function tasks()
{
    return $this->hasMany(Tasks::class, 'project_id');
}

-> Employee model

public function tasks()
{
    return $this->hasMany(Tasks::class, 'employee_id');
}
mehulkoradiya

$view_data['tasks'] = Tasks::join('project','tasks.project_id','=','project.id')->join('employee','tasks.employee_id','=','employee.id')->select('tasks.*','project.id as pro_id','project.project_title as pro_title','employee.id as emp_id','employee.first_name as emp_name')->sortable()->paginate(5);

i am trying to display name of employees work on this task

Yorki
Yorki
1 month ago (29,510 XP)

You will need to modify Tasks model. (btw. think of using singular name Task)

public function employees()
{
    return $this->belongsToMany(Employee::class, 'tasks_employee');
}

Also Employee model

public function tasks()
{
    return $this->belongsToMany(Tasks::class, 'tasks_employee');
}

You can get count of employees assigned to tasks this way

$tasks = Tasks::withCount('employees')->get();

foreach ($tasks as $task) {
    echo $task->employees_count;
}
mehulkoradiya

sir i am trying to join tasks and employee table and query like FIND_IN_SET(tasks.employee_ids,employee_id). how to join in laravel

Yorki
Yorki
1 month ago (29,510 XP)

Try this

Tasks::join('project','tasks.project_id','=','project.id')
    ->join('employee', function ($join) {
        $join->on(DB::raw('find_in_set(tasks.employee_ids, employee.id)'), DB::raw(''), DB::raw(''));
    })
    ->sortable()
    ->paginate(5); 
mehulkoradiya

not find employees on employee_ids like ["3","1"] join employee table but not fetch coma represented value. only join employee record on employee_ids like 2 not join ["2"]

Please sign in or create an account to participate in this conversation.