0

I have 2 tables I want to join.

table: users table: orders

users and orders have a column called 'id'

I need to find all the orders with status 3 for users that have rep_id = 112:

select * from `orders` 
inner join `users` on `orders`.`user_id` = `users`.`id`
where `status` = 3
and `rep_id` = 112

The problem is, the result I'm getting puts the users.id in the id column and in user_id (From orders table) column.

I need the orders.id not the users.id to be in the id column in the results.

Should I just not use Eloquent for that?

Sigal Zahavi
  • 1,045
  • 2
  • 21
  • 42
  • Possible duplicate of [How to select columns from joined tables: laravel eloquent](https://stackoverflow.com/questions/45437607/how-to-select-columns-from-joined-tables-laravel-eloquent) – Shuvojit May 14 '19 at 09:51
  • I looked at this post, and tried this: $sales = Order::where('status', 3) ->with(['user' => function ($q) { $q->where('rep_id', Auth::id()); }]) ->get(); It gave me all the orders and not just the ones for users with rep_id = Auth::id() The SQL it created: select * from `orders` where `status` = ? I also want to be able to use the relationship on it, e.g. do: foreach($sales as $sale) { echo $order->user->fullName() } – Sigal Zahavi May 14 '19 at 10:07
  • This solved the problem: $sales = Order::whereHas( 'user', function ($query) { $query->where('rep_id', Auth::id()); } ) ->with('user') ->get(); – Sigal Zahavi May 14 '19 at 10:20

0 Answers0