Querying Many to Many with Conditions

user_1495000 0
Viewed 309

I have a gorm many2many model like this:

type User struct {
    gorm.Model
    Username    string  
    LikedBooks []Books `gorm:"many2many:user_liked_books;"`
}

type Book struct {
    gorm.Model
    Name        string 
    Likes       []User `gorm:"many2many:user_liked_books;"`
}

Where a User can like many Books and a Book can have many Users that like it. I now want to query for Books that have been liked, and return the top 50 liked books. How can I achieve that using gorm? I dont understand how to query with conditions on book.liked = true, sorted by liked count, limited by 50.
I couldnt find an example like that in the docs or on stackoverflow.

go-gorm go
1 Answers

This could be done in the same way you would construct a SQL query for the request you described, just using the gorm functions. It could look something like this:

var books []Book
tx := db.Table("books").
      Joins("INNER JOIN user_liked_books ulb ON ulb.book_id = books.id").
      Select("books.id, books.name, count(ulb.user_id) as likes_count").
      Group("books.id, books.name").
      Order("likes_count desc").
      Limit(50).
      Find(&books)

If you would also want to load the Likes field, try adding .Preload("Likes") to the construct above.

user_75675790
Related