Eloquent查询固定升序排序及SQL转Eloquent实现问题咨询

1. Setting a Default asc Order for All Eloquent Queries #

If you want every Eloquent query on a specific model (or all models) to default to ascending order, a global scope is the cleanest, most maintainable approach. Here's how to set it up:

Step 1: Create the Global Scope Class #

namespace App\Scopes;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class AscendingOrderScope implements Scope
{
    public function apply(Builder $builder, Model $model)
    {
        // Use the model's primary key by default, or replace with your preferred sort column
        $builder->orderBy($model->getKeyName(), 'asc');
    }
}

Step 2: Register the Scope in Your Model #

Add this to the model you want to apply the default order to (or a base model if you want it across all your models):

namespace App\Models;

use App\Scopes\AscendingOrderScope;
use Illuminate\Database\Eloquent\Model;

class YourModel extends Model
{
    protected static function booted()
    {
        static::addGlobalScope(new AscendingOrderScope);
    }
}

Now every query for this model will automatically include ORDER BY your_model.id ASC unless you explicitly override it with orderBy(..., 'desc').

2. Fixing the Truncated Query for Your Join/GroupBy Logic #

Looking at your current Eloquent code, the root issue is how you're using DB::raw() with orderBy. The second parameter of DB::raw() is for query bindings, not the sort direction—this misusage is causing your order clause to get truncated.

Original SQL You Want to Replicate: #

SELECT locals.* FROM prises JOIN locals ON prises.liaison_id = locals.id GROUP BY locals.id ORDER BY COUNT(liaison_id);

(Note: Your original SQL doesn't specify DESC, but your Eloquent code tried to use it—adjust the direction below to match your needs!)

Corrected Eloquent Query: #

Here's the fixed version that generates the correct, untruncated SQL:

return $query->select('locals.*')
    ->from('prises')
    // Simplified join syntax (no need for a closure here since it's a basic on clause)
    ->join('locals', 'prises.liaison_id', '=', 'locals.id')
    ->groupBy('locals.id')
    // Move the sort direction to the second argument of orderBy(), not DB::raw()
    ->orderBy(DB::raw('COUNT(prises.liaison_id)'), 'DESC')
    // OR, to match your original SQL's ascending order (no DESC):
    // ->orderBy(DB::raw('COUNT(prises.liaison_id)'));

Quick Breakdown of the Fix: #

Your original code passed 'DESC' as the second argument to DB::raw(), which is meant for binding values (like DB::raw('name = ?', ['John'])). By moving the sort direction to the orderBy() method's second parameter, you ensure the full order clause is included in the final SQL.

Also, if you're using Laravel 5.2+, double-check that your config/database.php has strict mode enabled (or confirm that GROUP BY locals.id complies with MySQL's ONLY_FULL_GROUP_BY mode—since locals.id is the primary key, selecting locals.* here should be safe).

内容的提问来源于stack exchange，提问作者Linoo