I have a main table, "help_pages" which stores id's of either articles or videos. There are two other tables: "articles" and "videos."
In the example below, $portalType is either 'reseller' or 'global' for the purposes of my application. All was well as long as I didn't have a flag on videos to be unpublished. Now I'm having trouble filtering the scope function by BOTH articles and videos that are published.
This is in my models/HelpPage.php:
public function scopeByPortalType($query, $portalType) {
return $query->where('portal_type', '=', $portalType)
->leftjoin('articles', 'content_id', '=', 'articles.id')
->where('articles.published', '=', '1');
}
what I wish I could do is just add a second
->leftJoin('videos', 'content_id', '=', videos.id')
->where('videos.published', '=', '0');
but this returns too many rows. I tried creating a temp table with a UNION for both articles and videos:
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_assets AS
(SELECT id, 'Article' AS content_type FROM articles WHERE published = 1)
UNION
(SELECT id, 'Video' AS content_type FROM videos WHERE published = 1)
and then joining that, but no dice. This might be a smaller deal than I'm making it, but I'm lost now!
Laravel version 4.2
The Eloquent way to do this would be setting up the relationships so you could utilize eager loading.
Relationships: http://laravel.com/docs/4.2/eloquent#relationships
Eager Loading: http://laravel.com/docs/4.2/eloquent#eager-loading
Setup relations:
Then you can utilize eager loading like this:
You might be able to then leverage whereHas and orWhereHas on the actual query rather than my example above.