I am trying to write a MySQL select query using Laravel's Database Query Builder
I have this mysql query:
SELECT * FROM `tweets` WHERE `user_id` = 1 OR `user_id` in (SELECT `follows_id` from `follows` where `user_id` = 1)
I am trying to write it for Laravel
$users = DB::table('tweets')
->where('user_id', '=', 1)
how can this be done?
You can do something like this even though it looks ugly.
$tweets = DB::table('tweets')
->where('user_id', 1)
->orWhereIn('user_id', DB::table('follows')->select('follows_id')->where('user_id', 1)->pluck('follows_id'))
->get();
I would suggest a SQL rewrite as OR and IN(SELECT ...) tends to optimize badly.
The SQL result might be wrong as you didn't provide example data and expected result see Why should I provide a Minimal Reproducible Example for a very simple SQL query? for providing those.
SELECT
tweets.*
FROM
tweets
WHERE
tweets.user_id = 1
UNION ALL
SELECT
tweets.*
FROM
tweets
INNER JOIN
follows ON tweets.user_id = follows.follows_id
WHERE
follows.user_id = 1
I believe the following Laraval code should do that. But not sure as i didn't program in Laravel for some time now.
<?php
$first = DB::table('tweets')
->select('tweets.*')
->where('user_id', '=', 1);
$second = DB::table('tweets')
->select('tweets.*')
->join('follows', 'tweets.user_id', '=', 'follows.follows_id')
->where('follows.user_id ', '=', 1)
->union($first)
->get();
?>
Tweetmodel, aUsermodel and associate them, then you should be able to do$result = Tweet::whereHas("User", function($subQuery){ $subQuery->where("user_id", "=", 1); })->get();SELECT * FROM tweets WHERE tweets.user_id = 1 UNION ALL SELECT * FROM tweets INNER JOIN follows ON tweets.user_id = follows.follow_id WHERE follows.user_id = 1as it is more optimal if performance mattersORandIN(SELECT ..)tends to optimize to bit badly in MySQL.orWhereHas()clause. It's a little hard to answer this in Laravel terms due to having to guess at the relationships/query logic. I try to avoid the usage ofDB::table()in favour of Models, but your answer should work fine.