Laravel 5.1 Query Builder：获取帖子，其主体包含搜索查询

Assumption:

1. This is a normal blog app.
2. users table has id, name, email, password fields.
3. posts table has id, title, body fields.
4. 5 users are registered.
5. Each user has 10 posts.
6. User "hasMany" posts, Post "belongsTo" a User.

Question:

I want to get "users" who have posts whose title or body contain search queries.

My current code:

// example of search queries
$search_queries = ['aaa', 'bbb', 'ccc'];

// column names to search in MySQL
$search_column_names = [
                        'users.name',
                        'posts.title',
                        'posts.body'
                        ];

$users = User::leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->orWhere(function($q) use ($search_queries, $search_column_names){
        foreach ($search_column_names as $key => $name) {
            $q->orWhere(function($q) use ($search_queries, $name){
                foreach ($search_queries as $key => $search_query) {
                    $q->where($name, 'like', '%' . $search_query . '%');
                }
            });
        }
    })->distinct('users.id')->get();

but still I get more than 5 users with different id(up to 50). I though distinct(users.id) gave me an ideal result, but it didn't work.

I searched through web already, but I couldn't find a solution.

Thanks in advance.

You can use a raw statement.

$users = User::whereRaw("id in(
                            select distinct user_id from posts
                            where title like :query or
                            body like :query
                            )" , ["query" =>  "%" . $search_query . "%"])->get();

I found a solution. I divided posts table searches into two whereIns. Thanks!

// column names to search in the database
$search_column_names = [
'users.name'
];

$users = User::
orWhere(function($q) use ($queries, $search_column_names){
    foreach ($search_column_names as $key => $name) {
        $q->orWhere(function($q) use ($queries, $name){
            foreach ($queries as $key => $value) {
                $q->orWhere($name, 'like', '%' . $value . '%');
            }
        });
    }
})
->whereIn('users.id', function($q) use ($queries){
    $q->select('user_id')
    ->from(with(new Post)->getTable())
    ->where(function($q2) use ($queries){
        foreach ($queries as $key => $value) {
            $q2->orWhere('posts.title', 'like', '%' . $value . '%');
        }
    });
}, 'or')
->whereIn('users.id', function($q) use ($queries){
    $q->select('user_id')
    ->from(with(new Post)->getTable())
    ->where(function($q2) use ($queries){
        foreach ($queries as $key => $value) {
            $q2->orWhere('posts.body', 'like', '%' . $value . '%');
        }
    });
}, 'or')
->get();