CODEWITHSUNDEEP
phplaravel
mafortis
mafortis

Reputation: 7128

Laravel query join 1 table in 2 tables at single query

I have a complex query and it works, now i need to join users table and get 2 different value of it based on 2 different tables here is where things messed up.

Code

I have commented issue part as well as data that should be returned

$datas = DB::table('projects')
    ->orderBy('projects.id', 'desc')
    ->join('customers', 'projects.customer_id', '=', 'customers.id')
    ->leftjoin('project_schedules', 'projects.id', '=', 'project_schedules.project_id')
    ->leftjoin('project_schedule_details', 'project_schedules.id', '=', 'project_schedule_details.schedule_id')
    ->leftjoin('project_schedule_visits', 'projects.id', '=', 'project_schedule_visits.project_id')


    // issue tables (project_admins) and (project_shoppers)
    ->leftjoin('project_admins', 'projects.id', '=', 'project_admins.project_id')
    ->leftjoin('project_shoppers', 'projects.id', '=', 'project_shoppers.project_id')


    ->groupBy('projects.id')
    ->select(
        'projects.name as project',
        'customers.companyName as customer',
        'project_schedule_details.date as schedule_date',
        'project_schedule_details.description as schedule_description',
        'project_schedule_details.actual_cost as schedule_actual_cost',
        'project_schedule_visits.from_date as visit_from_date',
        'project_schedule_visits.to_date as visit_to_date',
        'project_schedule_visits.description as visit_description'
        // 'project_admins.user.username as admin'  //i.e. user 1
        // 'project_shoppers.user.username as shopper' // i.e user 2
    )
    ->get();

Explanation:

I need to join users table to project_admins table as well as project_shoppers table and return related users to those tables.

Then I would have user 1 returned based on project_admins table And user 2 based on project_shoppers table.

Any idea how to achieve that goal?

Upvotes: 0

Views: 232

Answers (1)

Sjoerd Loeve
Sjoerd Loeve

Reputation: 231

Under the leftJoins you can add the extra joins for getting the users:

->leftJoin('users','users2.id','=','project_admins.user_id')
->leftJoin('users AS users2','users2.id','=','project_shoppers.user_id')

In your select you can get the details like this:

->select(
...,
'users.username AS admin',
'users2.username AS shopper'
)

Upvotes: 1

Related Questions