CODEWITHSUNDEEP
mysqllaravel
Stefan Avramovic
Stefan Avramovic

Reputation: 1353

Laravel's Database Query Builder

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?

Upvotes: 0

Views: 68

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

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();
?>

Upvotes: 1

nakov
nakov

Reputation: 14248

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();

Upvotes: 2

Related Questions