CODEWITHSUNDEEP
laravellaravel-5
Mostafa Norzade
Mostafa Norzade

Reputation: 1766

Creating subquery in laravel

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 )

Upvotes: 0

Views: 42

Answers (2)

Ahmed Nour Jamal El-Din
Ahmed Nour Jamal El-Din

Reputation: 1011

You can use has method with the corresponding relationship.

To do this:

  1. Create User and Post models.
  2. Define the relationships between the models, e.g. User has many Post with posts as relationship and Post belongs to User.
  3. 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

Upvotes: 1

Mozammil
Mozammil

Reputation: 8750

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.

Upvotes: 2

Related Questions