Let's take the query from this post as example

1
2
3
4
5
6
7
8
9
10
11
12
SELECT books.id AS book_id
, books.isbn
, books.title
, t_borrowers.user_ids
FROM books
LEFT JOIN (
SELECT user_books.book_id
, CONCAT('#', GROUP_CONCAT(user_books.user_id SEPARATOR '#,#'), '#') AS user_ids
FROM user_books
GROUP BY user_books.book_id
) AS t_borrowers ON t_borrowers.book_id = books.id
WHERE (t_borrowers.user_ids LIKE '%#1#%' OR t_borrowers.user_ids LIKE '%#3#%');

See there's a sub-select inside? Let's see how to construct this query in Laravel

\App\Models\Book class map to the table books

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php

// inner select
$subquery = \DB::table('user_books')
->select([
'user_books.book_id',
\DB::raw('CONCAT(\'#\', GROUP_CONCAT(user_books.user_id SEPARATOR \'#,#\'), \'#\') AS user_ids'),
])->groupBy('user_books.book_id');

$query = \App\Models\Book::select([
\DB::raw('books.id AS book_id'),
'books.isbn',
'books.title',
't_borrowers.user_ids',
])
->leftJoinSub($subquery, 't_borrowers', function ($join) {
$join->on('t_borrowers.book_id', '=', 'books.id');
})
->where(function ($q) {
$q->orWhere('t_borrowers.user_ids', 'LIKE', '#' . 1 . '#')
->orWhere('t_borrowers.user_ids', 'LIKE', '#' . 3 . '#');
});

By using the leftJoinSub to construct the query