Laravel query builder vs Raw

Question

Im quite new to laravel and so far I realy enjoy eloquent and querybuilder, but as soon as the queries become more complex, my head starts to hurt... I have just finished 2 working examples after quite some time, maybe you guys can help me optimize it. I will first give the examples, then the (dis)advantages

So first the DB::select... which honestly, I think is more readable then the 2nd example.. or maybe I am just doing the builder thing wrong xD.

    $shared_slugs   = Magic::in_query($this->shared_slugs);

    $items = DB::select("
        SELECT * FROM f_admin_items
        WHERE
            active = 1
        AND (
            slug IN $shared_slugs
            OR
            :treshhold <= :urank AND id IN (SELECT admin_item_id FROM f_user_access WHERE user_id = :uid)
            OR
            :treshhold > :urank AND `rank` >= :urank
        )
        ORDER BY `order` ASC
    ", [
        'treshhold' => $this->threshold_rank,
        'urank' => $user_rank,
        'uid'    => $user_id
    ]);

Overall pretty effective with the named parameter binding. Basicly the menu items always have to be active, plus (1 OR 2 OR 3 ). For example dashboard is accepted as shared slug.. Otherwise there are some ranking checks.

Now I have been trying hard to do this with eloquent en query builder =/ I didn't think setting up a relation was not necessary because I only use this for the menu and a middleware. In this case the f_user_access table containing only admin_item_id and user_id, doesn't really function as a pivot and isn't used anywhere else.

    $items =
    $this->where( 'active', 1 )
         ->where( function ($query) {

              $query->whereIn( 'slug', $this->shared_slugs )
                    ->orWhere(function ($query) {

                        $query->whereRaw( $this->threshold_rank.' <= '.$this->user_rank )
                              ->whereIn('id', function ($query) {

                                  $query->select('admin_item_id')->from('f_user_access')->where('user_id', $this->user_id)->get();
                              });
                     })
                     ->orWhere(function ($query) {

                        $query->whereRaw( $this->threshold_rank.' > '.$this->user_rank )
                              ->where( 'rank', '>=', $this->user_rank );
                     });
         })->orderBy( 'order', 'ASC' )->get();

What I like about this second one is the fact I can pass $shared_slugs as an array, I don't have to convert it into a string first. But apart from that I really hate the looks of this, where(function($query){}) etc etc... On top of that the $ids passed to this function, are not accessible in the where.functions, so I had to define them in the class first.

The first one I like because of the named binding and it doesn't read that bad to be :S, also the vars are accessible.. Downside, I have to call this function to convert the shared slugs into an array.

Is it really that bad to not use eloquent and querybuilder? at least in some cases... and what would you guys do to make the second example better? =/

UPDATE::

Due to the answers and feedback, I ditched the raw sql. The current function has 5 scopes in it, and a (small) model is made for the user_access.

    $items =
    $this->active() //Only active items AND... 1, 2 or 3

        ->where( function ($query) {

            $query->shared_slug()             //  1

            ->orWhere(function ($query) {      // OR  2
                $query->access_required()
                ->whereIn('id', UserAccess::get_access( $this->user_id ) );
            })

            ->orWhere(function ($query) {      // OR  3
                $query->no_access_required()
                ->whereRaw( 'rank >= '.$this->user_rank );
            });

        })->ASC()->get(); 

Answer 1

The major benefit of using the query builder is it abstracts you away from the language used by your storage of choice, i.e. MySQL, Oracle, SQLite, etc. If you ever switch database types, you can be stuck with a lot of refactoring raw SQL. Believe me, it's not pretty when you start on a project and learn this is the case.

There are always caveats, however, which is precisely why Eloquent has the capability to work with raw statements, as well.