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

Question

With convenient areas for adding vibrant patterns:

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

But how to add scope to DB::table ?

I use this query to browse pages:

 $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 requests, but with the same where conditions. Therefore, I would like to define my where conditions only once and reuse them in all other DB::table view queries.

Answer 1

DB::table does not support scopes. You can simply write a small function that performs some actions with the request and returns it. The syntax is not so 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 slightly 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

Thank you answer lukasgeiter I had an idea to create a class for this that extends DB and returns the beginning of the query on which to build:

 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; } } 

Now I can use this to create several different queries, all with the same conditions.

Get the number of views 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 in 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();