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?
Advertisement
Answer
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' )