Sorting the list of users in Laravel 5.8

Question

I am beginner in Laravel. I use Laravel 5.8 in my project.

I have this code:

Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('company_id')->unsigned();
            $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
            $table->boolean('enable')->default(0);
            $table->string('name', 120)->nullable();
            $table->string('surname', 120)->nullable();
            $table->string('email', 120)->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->bigInteger('counter')->default(0);
            $table->string('url_address', 160);
            $table->string('account_paid_for', 12)->nullable();
            $table->string('premium_for', 12)->nullable();
            $table->string('hits', 12)->nullable();
            $table->rememberToken();
            $table->timestamps();
            $table->engine = "InnoDB";
        });


public function getUserList(string $query, string $sortColumn, string $sortMethod)
    {
        if ($query != "") {
            return User::ofRoleType(['user', 'userPremium', 'userCompany', 'userSponsor', 'userGuest'])
                ->where(function ($q) use ($query, $sortColumn, $sortMethod) {
                    $q->where('email', 'LIKE', '%' . $query . '%')
                        ->orWhere('id', 'LIKE', '%' . $query . '%')
                        ->orWhere('name', 'LIKE', '%' . $query . '%')
                        ->orWhere('surname', 'LIKE', '%' . $query . '%')
                        ->orderBy($sortColumn, $sortMethod);
                })->paginate(2);
        } else {
            return User::ofRoleType(['user', 'userPremium', 'userCompany', 'userSponsor', 'userGuest'])->paginate(2);

        }
    }

and User model:

class User extends Authenticatable implements MustVerifyEmail
{
    use Notifiable;
    use cms\Presenters\UserPresenter;

    public static $roles = [];


    protected $fillable = ['name', 'surname', 'email', 'email_verified_at', 'password', ];

    protected $hidden = [
        'password', 'remember_token',
    ];


    public function roles()
    {
        return $this->belongsToMany('App\Role');
    }

    public function mainRole()
    {
        return $this->hasOne('App\Role');
    }


    public function hasRole(array $roles)
    {

        foreach ($roles as $role) {

            if (isset(self::$roles[$role])) {
                if (self::$roles[$role]) return true;

            } else {
                self::$roles[$role] = $this->roles()->where('name', $role)->exists();
                if (self::$roles[$role]) return true;
            }

        }
        return false;
    }

}

I need to display a list of users in the following order:

1. First, users who have a premium account (ie a premium_for field with a larger date than today),

2. Then users with the largest number in the field "hits"

3. Other users

How can you do this? Does anyone know how to customize my getUserList function for this sort?

Answer 1

I am light on Laravel experience, but I think you are asking for:

->orderBy(DB::raw('IF(premium_for > CURDATE(), 0, 1)'))
->orderBy('hits', 'DESC');

(Untested) This means to state:

1. Priority to anyone with premium expiry in the future. (Doesn't matter how much in the future.)
2. Then sort by hits - biggest to smallest.

A resource explaining DB::raw(): https://laraveldaily.com/select-with-dbraw-make-your-database-work/

I'll also recommend that you WS_CONCAT() your WHERE columns and only make one LIKE comparison. Like this: 'Where like' clause using the concatenated value of 2 columns with eloquent