Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

cooperino's avatar

Where is my mistake in the conversion from SQL to Query Builder?

I am trying to convert an SQL query to Query Builder.

The SQL query:

SELECT DISTINCT(items_to_list.user_id), items.item_code
FROM items
LEFT JOIN items_to_list ON items_to_list.user_id = users.id
WHERE items_to_list.list_id IN (10,20);

My Query Builder attempt:

DB::table('items')->select('items_to_list.user_id', 'items.item_code')->distinct()
->leftJoin('items_to_list','user_id', '=', 'items.id')
->where('items_to_list.status', '=', 0)
->where('items.deleted', '=', 0)
->whereIn('items_to_list.list_id', [10, 20])->pluck('items_to_list.user_id')->toArray();

I get 0 results

0 likes
2 replies
Sinnbeck's avatar
Sinnbeck
Best Answer
Level 102

Well first of you arent passing anything to distict

DB::table('items')->select('items_to_list.user_id', 'items.item_code')
->distinct('items_to_list.user_id')
->leftJoin('items_to_list','user_id', '=', 'items.id')
->where('items_to_list.status', '=', 0)
->where('items.deleted', '=', 0)
->whereIn('items_to_list.list_id', [10, 20])->pluck('items_to_list.user_id')->toArray();
1 like

Please or to participate in this conversation.