Order by last name

Question

I am trying to set the default scope for my User model. Each user has one name column. The problem is that I would like to order users by the first letter of their last name. This would be the start of the last word from the name. For example, a users name may be "Kevin John Smith", I would like to order by Smith. I currently have default_scope order('name ASC'), but this sorts by the first letter of the first name. How would I convert this to sort by the first letter of the last name? Thanks!

Answer 1

Try this:

User.select("users.*, SUBSTRING_INDEX(users.name, ' ', -1) AS lastname").limit(10).order('lastname ASC')

SUBSTRING_INDEX is one of the mySQL string functions.

For PostgreSQL split_part should work:

User.select("users.*, split_part(users.name, ' ', 3) AS lastname").limit(10).order('lastname ASC')

I am not sure, though try with -1 too:

User.select("users.*, split_part(users.name, ' ', -1) AS lastname").limit(10).order('lastname ASC')

Because, the latter one will ensure that the last string after split is used which will cover the cases where user has just first name and last name.

Make sure you use unscoped, which returns a scope for this class without taking into account the default_scope.