Sort records with an associated field

I want to order records with associated records in GORM V2.

I have two structs:

type Client struct {
    gorm.Model
    UUID                   uuid.UUID `gorm:"type:uuid"`
    ClientProfile          ClientProfile
}

type ClientProfile struct {
    gorm.Model
    ClientID         uint
    FirstName        string
    LastName         string
}

Now, I can load all the records with:

db.
    Preload(clause.Associations).
    Find(&clients).

How can I now order these records with a field from the ClientProfile struct?

I tried without success:

db.
    Preload(clause.Associations).
    Order("client_profiles.last_name DESC").
    Find(&clients)

This throws the following error:

ERROR: missing FROM-clause entry for table “client_profiles” (SQLSTATE 42P01)

Answer

Find with Preload queries the clients table first, and then queries the client_profiles table with the IDs from the first, thus it can’t sort the first query by data from the second one.

Since this is a HasOne relationship you can use Joins Preloading which actually does use a single query for both entities. Then you will be able to sort by the related entity’s field:

err := db.
    Model(&Client{}).
    Joins("ClientProfile").
    Order("client_profiles.last_name DESC").
    Find(&clients).
    Error
// handle error