New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Proposal] Query builder 'reorder' method #204

Open
derekmd opened this Issue Sep 13, 2016 · 0 comments

Comments

Projects
None yet
1 participant
@derekmd

derekmd commented Sep 13, 2016

For growing Laravel projects, there is a need to re-use model relationships and scopes. For query builders, two common problems I've come across are:

  1. Inability to use query builder count() on a relationship that orders the results when eager-loaded.

    class Gallery extends Model
    {
       public function photos()
       {
           return $this->morphMany(Photo::class, 'parentable')->orderBy('photo_order');
       }
    }

    For $gallery->photos()->count(), this statement will generate Postgresql error:

    column "photos.photo_order" must appear in the GROUP BY clause or be used in an aggregate function
    

    This use case isn't a show-stopper since you can use the eager-loaded photos property's Collection count() method when the operation's memory footprint isn't too expensive. However:

  2. Scopes and relationships are unable to remove an existing order (added by a preceding scope) or re-sort the relationship it re-uses.

    class Album extends Model
    {
       public function reviews()
       {
           return $this->hasMany(Review::class)
               ->with('reviewer')
               ->orderBy('created_at', 'desc');
       }
    
       public function rankedReviews()
       {
           return $this->reviews()
               // Becomes secondary sort column but we want it as primary
               ->orderBy('rating', 'desc');
       }
    }

Introducing reorder() to class Illuminate\Database\Query\Builder

Below is an example implementation macro:

<?php

namespace App\Providers;

use Illuminate\Database\Query\Builder;
use Illuminate\Support\ServiceProvider;

class QueryBuilderMacros extends ServiceProvider
{
    public function boot()
    {
        Builder::macro('reorder', function() {
            $property = $this->unions ? 'unionOrders' : 'orders';

            $this->{$property} = null;

            if (func_num_args() > 0) {
                return $this->orderBy(...func_get_args());
            }

            return $this;
        });
    }

    public function register()
    {
    }
}

This reorder() implementation does not affect global scopes applied on the Eloquent model so the
withoutGlobalScopes() method must be called to exclude orderBy() added in such a scope.

With the above examples, you can call $gallery->photos()->reorder()->count() to avoid an SQL aggregation function error and change the secondary Album@rankedReviews() model relationship to:

public function rankedReviews()
{
    return $this->reviews()
        ->reorder('rating', 'desc');
}

These are simple examples that will illicit the response, "uh, just copy and paste a few extra lines, then?" But when projects reach into majestic monolith proportions, report queries containing 5+ scopes scream for the ability to re-start the query's ordering at any time.

I originally added this reorder() ability in Laravel 5.1 but have since learned Ruby on Rails has its own Active Record reorder that exists to work around the same problems.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment