CODEWITHSUNDEEP
mysqllaravellaravel-5
horse
horse

Reputation: 725

How to query only one foreign row in a one-to-many relationship in Laravel 5

I have 4 models: posts, users, categories, userCategories.

posts-users have one-to-many relationship (one post belongs to a user).

posts-categories have one-to-many relationship (one post belongs to a category).

users-userCategories have one-to-many relationship (one user has many userCategories). userCategory has a rank field which holds the performance of the user for the specific category.

I query some posts with this:

$posts = Post::with(array('user.userCategories')
                )
            ->where('category_id', '=', $category_id)
            ->get();

After evaluating posts, I try to get the user's the userCategory rank for the post's category.

$rank_of_the_post_owners = array();
foreach($posts as $post)
    foreach($post->user->userCatergories as $userCategory)
        if($userCategory->id == $post->category_id) {
            $rank_of_the_post_owners [$post->id] = $userCategory->rank;
            break;
        }
    }
}

To get rid of the code above, I want to query only related userCategory (for the post's category). So that, I want to access the rank information like user.userCategory->rank instead of the loop.

How can I do that?

Upvotes: 0

Views: 37

Answers (1)

Rwd
Rwd

Reputation: 35180

When using with() you can constrain the relationship by using a closure as the value and since you already have the $category_id you could do something like:

$posts = Post::with(['user.userCategories' => function ($query) use ($category_id) {
        $query->where('userCategories.id', $category_id);
    }])
    ->where('category_id', '=', $category_id)
    ->get();

Then if you wanted to you could add an accessor to get the ranks for the posts:

public function getRankAttribute()
{
    return $this->user->userCategories->sum('rank');
}

So to access the rank you would just need to:

$posts->first()->rank;

https://laravel.com/docs/5.4/eloquent-mutators#accessors-and-mutators

Hope this helps!

Upvotes: 1

Related Questions