CODEWITHSUNDEEP
phpsqllaravel
Anthony Raimondo
Anthony Raimondo

Reputation: 1721

Laravel Eloquent maximize efficiency but keep elegant structure

Im working in a sensitive section of my app and i need to make sure to minimize the number of querys. I can easily do this with a multiple joins. The question is: is there a way to do this with beauty?

Elequent relationships are a good place to start but most of the time it requires multiple query.

The eager loading method used in this article looks alot better but still requires at least 2 querys and uses a whereIn statement instead of a join.

Article Example Of Eager Loading:

$users = User::with('posts')->get();

foreach($users as $user)
{
  echo $user->posts->title;
}

Using Eager Loading, Laravel would actually be running the following 

select * from users
select * from posts where user_id in (1, 2, 3, 4, 5, ...)

My current solution is to use laravel scopes in a way not intented. 

    public static function scopeUser($query) // join users table and user_ranks
    {
        return $query->join('users', 'users.id', '=', 'posts.user_id')
                     ->join('user_ranks', 'users.rank_id', '=', 'user_ranks.id');
    }
    public static function scopeGroup($query,$group_id) // join feeds,group_feeds (pivot) and groups tables
    {
        return $query->join('feeds', 'feeds.id', '=', 'posts.feed_id')
                     ->join('group_feed', 'feeds.id', '=', 'group_feed.feed_id')
                     ->join('groups', 'groups.id', '=', 'group_feed.group_id')
                     ->where("groups.id","=",$group_id);
    }

The resulting query looks like this:

$posts = Post::take($limit)
                ->skip($offset)
                ->user()                   // scropeUser
                ->group($widget->group_id) // scropeGroup
                ->whereRaw('user_ranks.view_limit > users.widget_load_total')
                ->groupBy('users.id')
                ->orderBy('posts.widget_loads', 'ASC')
                ->select(
                             'posts.id AS p_id',
                             'posts.title AS p_title',
                             'posts.slug AS p_slug',
                             'posts.link AS p_link',
                             'posts.created_on AS p_create_on',
                             'posts.description AS p_description',
                             'posts.content AS p_content',
                             'users.id AS u_id',
                             'users.last_name AS u_last_name',
                             'users.first_name AS u_first_name',
                             'users.image AS u_image',
                             'users.slug AS u_slug',
                             'users.rank_id AS u_rank',
                             'user_ranks.name AS u_rank_name',
                             'user_ranks.view_limit AS u_view_limit'
                         )
                ->get();

Because of column name collisions i then need a huge select statement. This works and produces a single query, but its far from sexy!

Is there a better way to deal with big joined querys?

Upvotes: 1

Views: 410

Answers (1)

lukasgeiter
lukasgeiter

Reputation: 153070

You could try to actually add the selects with aliases in the scope.

Note: This is totally untested

public static function scopeUser($query) // join users table and user_ranks
{
    foreach(Schema::getColumnListing('users') as $column){
        $query->addSelect('users.'.$related_column.' AS u_'.$column);
    }
    $query->addSelect('user_ranks.name AS u_rank_name')
          ->addSelect('user_ranks.view_limit AS u_view_limit');

    $query->join('users', 'users.id', '=', 'posts.user_id')
          ->join('user_ranks', 'users.rank_id', '=', 'user_ranks.id');

    return $query;
}

There is also no need to alias the post columns with a p_ prefix... But if you really want to, add another scope that does that and use addSelect()

Upvotes: 1

Related Questions