How do I write a complex query in Laravel 5.3? I am trying but not getting the result I expect.
Query
SELECT * FROM (SELECT posts.post_id
FROM posts
WHERE ((posts.user_id = 1 AND posts.user_type = 'user')
OR (posts.user_id IN (1) AND posts.user_type = 'page'))) posts
WHERE posts.post_id > '0' ORDER BY posts.post_id DESC
Please help me write this using Laravel Query Builder.
@Punit Gajjar has provided one solution, but that's not quite what your question was given the terms Query Builder. His solution will work, but half it it doesn't make use of the query builder (it's just a copy/paste and throwing your SQL into a raw query, which is essentially exactly the same thing as you had before) and therefore I feel it's necessary to provide you with an additional option:
Post::where('post_id', '>', 0)
->where(function($query) {
$query->where(function($subquery) {
$subquery->where('user_id', 1)->where('user_type', 'user');
})->orWhere(function($subquery) {
$subquery->whereIn('user_id', [1])->where('user_type', 'page');
});
})
->orderBy('post_id', 'DESC')
->get();
I've kept variables names short just for readability purposes, but the argument in the anonymous functions ($query and $subquery) are the query builder instances.
Here is your answer .
$MyQuery = DB::table(DB::Raw("(SELECT
posts.post_id
FROM
posts
WHERE
(
(
posts.user_id = 1
AND posts.user_type = 'user'
)
OR
(
posts.user_id IN (1)
AND posts.user_type = 'page'
)
)
)"))
->where('posts.post_id','>',"0")->orderBy("posts.post_id" , "DESC")->get();
Try printing query once using ->toSql() like this
echo $MyQuery = DB::table(DB::Raw("(SELECT
posts.post_id
FROM
posts
WHERE((
posts.user_id = 1
AND posts.user_type = 'user'
)
OR(
posts.user_id IN (1)
AND posts.user_type = 'page'
)))"))
->where('posts.post_id','>',"0")->orderBy("posts.post_id" , "DESC")->toSql();
die();
