Hi all! I have already search in the issue tracker but no success here we go:

What version of Go are you using (go version)?

go version go1.14.2 darwin/amd64

Which database and its version are you using?

Postgres 12

The Question:

I have a simple belongs to association:

type User struct {
    gorm.Model
    Email       string
    Password    sql.NullString
    ...
}

// Association:
type AnotherUser struct {
    gorm.Model
    User        User
    UserID      int64
}

Then I would like to query a list of AnotherUser ordering by the email. I've tried:

var list []AnotherUser
db.Order("email asc").Find(&list)

But off course is not finding the field email because it does not belong to the AnotherUser entity. (even if I preload the query is not a join query)

The queries executed are:

SELECT * FROM "another_users"  WHERE "another_users"."deleted_at" IS NULL ORDER BY email asc

Is this kind of ordering something supported by the library? If not, is there any workaround?

Comment From: LonglyCode

Maybe like this?

var list []AnotherUser
db.Preload("User", func(db *gorm.DB) *gorm.DB {
            db = db.Order("email asc")
            return db
                }).Find(&list)

Comment From: foxluqi

Is there a way to auto join association table when order by an association field?

Comment From: afdecastro879

Regarding this I experienced the following: - What @LonglyCode mentioned before only works if what you want to order is the downstream list. In this use case that order is useless because the relation is one to one (you will be ordering the associated user by it's email and then joining it to the correspondent AnotherUser) - The only solution I found to order by an association field is the following:

var list []AnotherUser
db.Table("another_users").
    Select("another_users.*, users.*").
    Joins("INNER JOIN users ON users.id = another_users.user_id"). // After this you can order by any field
        Order("email asc")

The only caveat is that if the two tables you are joining have the same field name you must order using the table name as prefix. E.g. Order("users.email asc")

Comment From: dybxin

@LonglyCode

type User struct { ID uint64 json:"id" gorm:"primary_key" Email string Password sql.NullString ... }

// Cat: type Cat struct { ID uint64 json:"id" gorm:"primary_key" Name string Age int64 }

//Association type Association struct { UserID uin64 json:"user_id" CatID uint64 json:"cat_id" Sort uint json:"sort" }

when user's Preload("Cat") how to sort by field sort?

Comment From: PanithanK

@LonglyCode

type User struct { ID uint64 json:"id" gorm:"primary_key" Email string Password sql.NullString ... }

// Cat: type Cat struct { ID uint64 json:"id" gorm:"primary_key" Name string Age int64 }

//Association type Association struct { UserID uin64 json:"user_id" CatID uint64 json:"cat_id" Sort uint json:"sort" }

when user's Preload("Cat") how to sort by field sort?

Did you have a solution?

Comment From: slr71

I was able to get something like this to work by using a join in the custom preloading SQL function:

type Plan struct {
    // The plan identifier
    ID *string `gorm:"type:uuid;default:uuid_generate_v1()" json:"id,omitempty"`

    // The default quota values associated with the plan
    PlanQuotaDefaults []PlanQuotaDefault `json:"plan_quota_defaults,omitempty"`
}

type PlanQuotaDefault struct {
    // The identifier
    ID *string `gorm:"type:uuid;default:uuid_generate_v1()" json:"id,omitempty"`

    // The plan ID
    PlanID *string `gorm:"type:uuid;not null" json:"-"`

    // The default quota value
    QuotaValue float64 `gorm:"not null" json:"quota_value,omitempty"`

    // The resource type ID
    ResourceTypeID *string `gorm:"type:uuid;not null" json:"-"`

    // The resource type
    ResourceType ResourceType `json:"resource_type,omitempty"`

    // The effective date
    EffectiveDate time.Time `json:"effective_date,omitempty"`
}

// ResourceType defines the structure for ResourceTypes.
type ResourceType struct {
    // The resource type ID
    ID *string `gorm:"type:uuid;default:uuid_generate_v1()" json:"id,omitempty"`

    // The resource type name
    Name string `gorm:"not null;unique" json:"name,omitempty"`
}

The goal in this case is to order plan quota defaults by both resource type name and effective date. Here's a function that does that:

// GetPlanByID looks up the plan with the given identifier.
func GetPlanByID(ctx context.Context, db *gorm.DB, planID string) (*model.Plan, error) {
    wrapMsg := fmt.Sprintf("unable to look up plan ID '%s'", planID)
    var err error

    plan := model.Plan{ID: &planID}
    err = db.
        WithContext(ctx).
        Preload("PlanQuotaDefaults", func(db *gorm.DB) *gorm.DB {
            return db.
                Joins("INNER JOIN resource_types ON plan_quota_defaults.resource_type_id = resource_types.id ").
                Order("plan_quota_defaults.effective_date asc, resource_types.name asc")
        }).
        Preload("PlanQuotaDefaults.ResourceType").
        First(&plan).Error
    if err == gorm.ErrRecordNotFound {
        return nil, nil
    }
    if err != nil {
        return nil, errors.Wrap(err, wrapMsg)
    }

    return &plan, nil
}