Laravel的Eloquent ORM与Query Builder

Question

I have 2 tables: users and articles. users table has a column named is_suspended which accepts yes or no, and the articles table has a column named is_published which accepts 0 or 1. To fetch an article data from the database, articles.is_published must equals 1 and users.is_suspended must equals no. Now suppose that users.is_suspended equals yes, if I try to fetch data of an article using Query Builder like this:

// first code:

    $articles = Article::join('users', 'articles.user_id', '=', 'users.user_id')
        ->select(['articles.*', 'users.user_name', 'users.user_email'])
        ->where('articles.article_id', '=', 9)
        ->where('articles.is_published', '=', 1)
        ->where('users.is_suspended', '=', 'no')
        ->get();

This will work perfectly and will return nothing (because users.is_suspended = yes). Now let's try to fetch the same article with Laravel Eloquent ORM like this:

// second code:

$articles = Article::with([
    'user' => function($query) {
        $query->select(['user_id', 'user_name', 'user_email'])
            ->where('users.is_suspended', '=', 'no');
    }])
    ->where('articles.article_id', '=', 9)
    ->where('articles.is_published', '=', 1)
    ->get();

In this case I will get the next result:

[
  {
    article_id: 9,
    user_id: 1,
    article_title: "Article title here",
    article_body: "Article body here",
    is_published: 1,
    created_at: "2015-01-17 02:26:24",
    updated_at: "2015-01-17 02:26:24",
    user: null
  }
]

It will fetch data of the article even if the user is suspended, which is wrong. So my question is how to fix the second code to act like the first code?

Answer 1

You may want to use with to make sure you lazy load relationships but any constraints you put on it only apply to the loaded relations. To limit the response to articles where the user is not suspended you would want to use the whereHas method.

$articles = Article::with(['user' => function($q){
        $q->select(['user_id', 'user_name', 'user_email', 'created_at']);
    }])
    ->whereHas('user', function($q) {
        $q->where('is_suspended', '=', 'no');
    })
    ->where('articles.article_id', '=', 9)
    ->where('articles.is_published', '=', 1)
    ->get();