How do I stop GORM from sorting my preload by ID?

Question

In my database I have a users table, joined via a many-to-many table to schools. A school has many jobs. I'm trying to return all the schools and their jobs for a specific user. This is my code so far:

var user User
err := db.Where("id = ?", userID).Preload("Schools")
.Preload("Schools.Jobs", func(db *gorm.DB) *gorm.DB {
    return db.Order("job.job_reference DESC")
}).First(&user).Error
return &user.Schools, err

Gorm is then executing the following queries:

SELECT * FROM `user`  WHERE (id = 'foo') ORDER BY `user`.`id` ASC LIMIT 1

SELECT * FROM `school` 
INNER JOIN `user_school` ON `user_school`.`school_id` = `school`.`id` 
WHERE (`user_school`.`user_id` IN ('foo'))  

SELECT * FROM `job` WHERE (`school_id` IN ('1','2')) 
ORDER BY job.job_reference DESC,`job`.`id` ASC

The first two generated SQL queries are exectly what I expected however the last query tries to sort by the criteria I provided AND a default sort by ID. If I remove my specific sort instructions it still tries to sort by ID. How can I stop it from doing that?

Rebus

This is a great example why I have come to loathe ORMs. They make easy jobs easy, and they make difficult jobs impossible. – Lantz

@Flimzy I'm new to Go and Go-Gorm so hopefully I'm just at that part of the learning curve. I did realize I mistakenly had an ID on the Job object which explains why it tried to sort by a non existent field. So now it sorts on another field, in addition to the one I specified, but at least the query can run now. I'd still like to know how to get rid of the unnecessary additional sort though. I also don't like the unnecessary sql query for the user even though I only want schools and jobs, but I'm not sure how else to deal with the many-to-many relationship between user and school – Rebus

I don't mean this to be facetious, but is there a reason you need to use gorm? Why not use straight SQL (even if only for this particular query). You seem to know the SQL you want, so why not just use that? – Lantz

@laserjesus just a FYI, you can remove the Preload("Schools") as Preload("Schools.Jobs") will inflate both models for you. – Zipangu

@Flimzy I like compile time checking (to some extent) of my SQL queries. I like that I can switch database provider and my code will most likely be easy to port. The ORM handles avoiding injection attacks and other parsing steps that I don't need to think about. Also something like GORM also provides structs that I can reuse with hints for json serialization when I need to return results – Rebus

In order: 1. ORMs don't provide compile-time checking of your SQL anyway. But there are other ways to validate your SQL, such as with unit tests. 1b. You can use a query builder without an ORM. 2. Switching between SQL dialects is a nice benefit, but is, in my experience, always overblown. I've never seen even ORM code port nicely. I see this is a theoretical benefit that never actually lives up to its promise. When it does live up, it will be with incredibly simple code, where the benefit is actually negligible (going back to my "ORMs make easy jobs easy, and hard jobs impossible" mantra). – Lantz

2. Injection attacks were solved decades before ORMs were existed. This is 100% not a reason to use an ORM. The other "parsing steps" are provided by any query builder, without an ORM. 3. The struct parsing and serialization benefits are part of Go, not GORM. I use those exact same benefits in every DB app I write in Go, and never use an ORM. – Lantz

@Flimzy All valid points. My original question is really about the query building component of GORM and I would potentially still have that question using something like Squirrel for query building. The additional feature of GORM beyond building the query is the return of strongly typed objects which I prefer to some string or reflection based approach since I get compile time checking on any fields that I reference and I can also use refactoring IDE features to easily change the name of such fields throughout my code. – Rebus

For scanning SQL results into Go structs, the sqlx package is my preferred method. – Lantz

Answer 1

I had the same problem. When you use .First() it needs to be ordered by something. Just change in your example to use Scan instead:

err := db.Where("id = ?", userID).Preload("Schools")
.Preload("Schools.Jobs", func(db *gorm.DB) *gorm.DB {
    return db.Order("job.job_reference DESC")
}).Scan(&user).Error
return &user.Schools, err


source: https://github.com/go-gorm/gorm/blob/master/finisher_api.go#L113

Neuropathy

source permalink: github.com/go-gorm/gorm/blob/… – Traci

Answer 2

Use gorm Take function to get a single record for gorm without the default primary key order in First method. In our case we created some scopes and made a variadic function so that we could reuse the function

// FirstOnlinePayment implements PayRepository.
func (p *payRepository) FirstOnlinePayment(ctx *gin.Context, scopes ...func(db *gorm.DB) *gorm.DB) (entity.OnlinePayment, error) {
    conn := p.dbProvider.Get(ctx.Request.Context(), "")
    var item entity.OnlinePayment

    err := conn.Scopes(scopes...).Preload(clause.Associations).Take(&item).Error
    if err != nil && errors.Is(err, gorm.ErrRecordNotFound) {
        return entity.OnlinePayment{}, nil
    }

    return item, err
}

In our scopes, we have our custom order field and it was conflicting with primary key order

func (p *payService) getLatestInvoiceOnlinePayment(ctx *gin.Context, inv entity.Invoice, scopes ...func(db *gorm.DB) *gorm.DB) (entity.OnlinePayment, error) {
    scopes = append(
        scopes,
        repository.FilterByField("invoice_id", inv.ID),
        repository.FilterFieldByNULL("disabled_at"),
        repository.OrderByField("created_at", "DESC"),
    )

    return p.payRepository.FirstOnlinePayment(
        ctx,
        scopes...,
    )
}

and here is our scopes

func FilterByField[T any](fieldName string, args ...T) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        if len(args) > 0 {
            db.Where(fieldName+" IN ?", args)
        }

        return db
    }
}

func FilterFieldByNULL(fieldName string) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        db.Where(fieldName + " IS NULL")

        return db
    }
}

func OrderByField(f, d string) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        db.Order(f + " " + d)

        return db
    }
}

Sholapur