Laravel 4: How to add scope to DB::table?

Question

With Eloquent models adding scopes is easy:

public function scopeMyScope($query)
{
   // Do stuff to that $query
}

But how to add scope to DB::table?

I use this query to get page views:

$views = DB::table('page_views')
    ->where('id', $this->id)
    ->where('agent', 'NOT LIKE', '%bot%')
    ->count(DB::raw('distinct session, DATE(created_at)'));

I also show the most popular pages etc with other queries, but with the same where conditions. So I would like to only define my where conditions once and reuse them in all other page view DB::table queries.

Answer 1

DB::table doesn't have support for scopes. What you could do is simply write a little function that does some things with the query and returns it. The syntax isn't as nice but it works:

function applyScope($query){
    $query->whereNotNull('deleted_at');
    $query->where('foo', 'bar');
    return $query;
}

And then:

$query = DB::table('page_views')
    ->where('id', $this->id)
    ->where('agent', 'NOT LIKE', '%bot%');
$query = applyScope($query);
$views = $query->count(DB::raw('distinct session, DATE(created_at)'));

Or a bit a shorter syntax:

$views = applyScope( DB::table('page_views')
                       ->where('id', $this->id)
                       ->where('agent', 'NOT LIKE', '%bot%')
         )->count(DB::raw('distinct session, DATE(created_at)'));

Answer 2

Thanks to lukasgeiter answer I got the idea of creating a class for this, that extends DB and returns the beginning of a query that can be built upon:

class PageViewQueryBuilder extends DB {

    public static function table()
    {
        $query = parent::table('page_views')
            ->where('agent', 'NOT LIKE', '%bot%')
            ->where('agent', 'NOT LIKE', '%spider%')
            ->where('agent', 'NOT LIKE', '%crawler%')
            ;

        return $query;
    }
}

I can now use this to create a number of different queries, all with the same where conditions.

Get view count for a specific page:

$count = PageViewQueryBuilder::table()
    ->where('page_id', $id)
    ->count(DB::raw('distinct session, DATE(created_at)'));

Get all views for a specific page:

$views = PageViewQueryBuilder::table()
    ->where('page_id', $id)
    ->orderBy('created_at', 'DESC')
    ->groupBy('session', DB::raw('DATE(created_at)'))
    ->get();

Get the 10 most popular pages for the last three months:

$views = PageViewQueryBuilder::table()
    ->selectRaw('page_id as page_id, count(distinct session, DATE(created_at)) as page_views')
    ->whereRaw('created_at BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()')
    ->groupBy('page_id')
    ->orderBy('page_views', 'desc')
    ->limit(10)
    ->get();