如何使用eloquent在Laravel 4中运行查询

Question

I have read the documentation but I can't quite figure out how to run the following query in Laravel 4

SELECT
    COUNT(*)
FROM
    acl a,
    routes r
WHERE
    (a.user_id = 1 OR
     a.group_id IN(SELECT group_id FROM user_group_junction WHERE user_id = 1)) AND
     r.route = 'protected' AND
     a.routes_id = r.id;

So how would I run the query in Laravel 4 using eloquent?

Yes each table has a model and relationships are defined

Based on my selected answer the following is what I came up with (And works)

Acls::join('routes','routes.id','=','acl.routes_id')
                ->where('routes.route','=','protected')
                ->Where(function($in_parenthesis) use($user_id){
                    $in_parenthesis->whereIn('acl.group_id',function($where_in) use($user_id){
                            $where_in->select('group_id')
                                ->from('user_group_junction')
                                ->where('user_id','=',$user_id);
                            })
                    ->orWhere('acl.user_id','=',$user_id);
                })
                ->count();

Answer 1

Methods called on Eloquent models pass through to the Illuminate\Database\Eloquent\Builder class, which itself extends from the Illuminate\Database\Query\Builder class. This means that all the things you can do with the query builder, you can also do with Eloquent models. The exception being that you don't need to define the table.

So for example if you wanted to perform a join and a where like you've done above, you would just do:

$query = Acl::join('routes', 'acl.routes_id', '=', 'routes.id')
             ->where('routes.route', '=', 'protected');

$results = $query->get();

Obviously this isn't your whole query, but you can figure out the rest.