Laravel scope with two relations

Question

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

Answer 1

Well, it's not pefect, but it does what I need it to do:

public function scopeByPortalType($query, $portalType) {
    $q = $query
        ->whereRaw("
        (id IN (SELECT help_pages.id FROM help_pages INNER JOIN articles ON content_id=articles.id WHERE articles.published='1' AND content_type='Article')
        OR
        id IN (SELECT help_pages.id FROM help_pages INNER JOIN videos ON content_id=videos.id WHERE videos.published='1' AND content_type='Video'))")
        ->where('portal_type', '=', $portalType);
    return $q;
}

I wish I could've figured out a way to do it using actual Eloquent, but every step led me down a rabbit hole with a dead end. This worked!

Answer 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:

class HelpPage extends Eloquent {

    public function articles()
    {
        return $this->hasMany('Article');
    }


    public function videos()
    {
        return $this->hasMany('Video');
    }

}

Then you can utilize eager loading like this:

$help_pages = HelpPage::with(array('articles' => function($query)
{
    $query->where('published', '=', 1);

},
'videos' => function($query)
{
    $query->where('published', '=', 1);

}
))->get();

You might be able to then leverage whereHas and orWhereHas on the actual query rather than my example above.