Order by concatenated fields

My User model has the fields

protected $fillable = [
    'first_name',
    'nick_name'
]

and the methods

public function getNameAttribute() {
    return $this->nick_name ?: $this->first_name;
}

public function scopeOrdered($query) {
    return $query->orderBy('nick_name')->orderBy('first_name');
}

Now, when I retrieve all users like User::ordered()->get() the order is:

All users without a nick name, then first name A-Z
All users with a nick name A-Z, then first name A-Z

What I want is: If a nick name is set, then use it for sorting. If no nick name is set, use the first name.

My attempt would be to simply concatenate nick name and first name, but I don't know how to achieve this within the scope method.

Any ideas?

pmall

10 years ago

Level 56

Use orderByRaw and put your db engine statement to put the null/empty values at the end instead of in first. I know postgres can do this, I dont know for other.

raphael

10 years ago

Best Answer

Level 4

Thank you @pmall, orderByRaw did the trick.

public function scopeOrdered($query) {
    return $query->orderByRaw('CONCAT(nick_name, first_name)')->orderBy('last_name');
}

Please or to participate in this conversation.