CODEWITHSUNDEEP
phplaravelormeloquentlaravel-5.1
Musterknabe
Musterknabe

Reputation: 6081

Join Eloquent Model with condition

I have the three Models Post, Page and Category.

Each Page is assigned to one Category and each Post is assigned to one Page.

I have already defined the relationships in the models. What I want to do now is to use Eloquent ORM to get all Post-objects that are from a Page with a certain Category. So basically, in SQL I would need to do it like this

select p.* from posts p INNER JOIN pages pa ON pa.id = p.page_id where p.created_time > '2015-08-18 00:00:00' and pa.categories_id = 1 and p.isVisible = 1 order by p.total_count desc limit 100

I'm now somehow trying to do the same with Eloquent, but I'm stuck. My current code looks like this

// Getting all the top posts from facebook for today.
/** @var Builder $topPosts */
$topPosts = Post::where('created_time', '>', Carbon::today()->toDateTimeString());
if ($type !== null) {
    $topPosts = $topPosts->where('type', $type);
}
return $topPosts->orderBy('total_count', 'desc')
    ->visible()
    ->take($limit)
    ->get();

Now, I wanted to add the category, but I don't know how to do it. I tried these steps here:

$topPosts = $topPosts->with(['page' => function($query) use($categoryId){
    $query->where('page_categories_id', $categoryId);
}]);

and this one

$topPosts = $topPosts->with('page')->where('page_categories_id', $categoryId);

but none of them worked. How would I achieve that? I always get the error message

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories_id' in 'where clause' (SQL: select * from posts where created_time > 2015-08-18 00:00:00 and categories_id = 1 and isVisible = 1 order by total_count desc limit 100)

Upvotes: 1

Views: 982

Answers (1)

benJ
benJ

Reputation: 2590

It looks like you need to use whereHas() in place your with() statement there (http://laravel.com/docs/4.2/eloquent#querying-relations)

This should work, and is basically just querying Posts with associated Pages with the particular category_id. I haven't included your ordering and things..

$posts = Post::where('created_time', '>', Carbon::today()) // Eloquent shouldn't actually need the toDateTimeString()
            ->with('page') // leave this in to eager load the page
            ->whereHas('page', function($query) use ($categoryId) {
                $query->where('page_categories_id', $categoryId);
            })
            ->get();

Upvotes: 1

Related Questions