Creating subquery in laravel

Question

How can I convert the following SQL query into Laravel query builder?

select
    *
from
    users
where
    EXISTS (
    SELECT
        *
    from
        posts
    where
        posts.created_by = users.id )

Mozammil · Accepted Answer · 2019-01-27 14:42:35Z

2

The following should work:

DB::table('users')
    ->whereExists(function ($query) {
        $query->select('*')
            ->from('posts')
            ->whereRaw('posts.created_by = users.id');
    })
    ->get();

You can also take a look at the documentation.

edited Jan 27, 2019 at 14:42

answered Jan 27, 2019 at 14:40

Mozammil

8,77017 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mozammil Over a year ago

What do you mean? This will retrieve all users that have a record (or more) in posts.

Mozammil Over a year ago

Also as you can see from this example, it outputs the query you need :)

Mozammil Over a year ago

You are welcome. Please consider upvoting/marking it as the answer if it worked for you :)

Matthew Brown Over a year ago

Why use whereRaw instead of ->where('posts.created_by', '=', 'users.id') ?

Mozammil Over a year ago

Both would work. However whereRaw() can be slightly faster in this case.

Ahmed Nour Jamal El-Din · Accepted Answer · 2019-01-27 18:52:35Z

You can use has method with the corresponding relationship.

To do this:

Create User and Post models.
Define the relationships between the models, e.g. User has many Post with posts as relationship and Post belongs to User.
Use the relationship with has method like this: User::has('posts')->get() where posts is the name of the relationship in User model.

From docs:

When accessing the records for a model, you may wish to limit your results based on the existence of a relationship. For example, imagine you want to retrieve all blog posts that have at least one comment. To do so, you may pass the name of the relationship to the has and orHas methods:

// Retrieve all posts that have at least one comment...
$posts = App\Post::has('comments')->get();

So in your code, it will retrieve all users that have at least one post

Collectives™ on Stack Overflow

Creating subquery in laravel

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related