Unsupported relations for schema with has many relation in GORM

user_17419969 0
Viewed 732

I have 2 models with Gorm like below:

type Post struct {
    *gorm.Model
    ID              uint32           `gorm: "primarykey"`
    PostTitle       string           `gorm:"posttitle;not null"`
    PostSlug        string           `gorm:"postslug;unique;not null"`
    PostCategoryID  uint32           `gorm:"postcategoryid"`
    PostCollections []PostCollection `gorm:"many2many:post_collection;"`
    PostTags        []PostTag        `gorm:"many2many:post_tag; constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}

type PostCategory struct {
    ID            uint32 `gorm: "primarykey"`
    PostCatName   string `gorm:"postcatname;not null"`
    PostCatSlug   string `gorm:"postcatslug;unique;not null"`
    PostCatParent uint32 `gorm: "postcatparent"`
    Posts         []Post `gorm:"foreignKey:PostCategoryID"`
}

I want get all posts of a parent category (which has many child categories) with function like below:

func GetPostByCategory(c *fiber.Ctx) error {
    db := database.DB //connect database
    var post []models.Post
    var categories []models.PostCategory
    
cat := c.Params("catslug") //get slug value of parent category from route

    cate := GetCategoryChild(cat)  //get array of child category
    
    db.Model(&categories).Where("id IN (?)", cate).Association("Posts").Find(&post)

    return c.JSON(post)
}

It return nil array . Please help me to fix this

go-gorm go
1 Answers

Problem

It is in your db.Model(&categories), you use an empty array of PostCategory. This will result in this query:

Code:

    DB.Model(&categories).Where("id IN (?)", []int{1, 2, 3}).Association("Posts").Find(&post)

Output:

SELECT * FROM `posts` WHERE id IN (1,2,3) AND `posts`.`post_category_id` IN (NULL) AND `posts`.`deleted_at` IS NULL

The "id IN (?)", cate id is not the id of category, but the id of the post instead, because we are using Post type inside the .Find(&post)

You can try to apply logging into Gorm by using this config

databaseConfig := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?multiStatements=true&parseTime=true", "root", "", "127.0.0.1", "3306", "tester")

    DB, _ = gorm.Open(mysql.Open(databaseConfig), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })

Solution

Try to insert some data inside your categories

This is My Full Code, I comment several line of your code because no full code was provided.

// https://stackoverflow.com/questions/69977516/unsupported-relations-for-schema-with-has-many-relation-in-gorm
package main

import (
    "fmt"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

type Post struct {
    *gorm.Model
    ID             uint32 `gorm:"primarykey"`
    PostTitle      string `gorm:"posttitle;not null"`
    PostSlug       string `gorm:"postslug;unique;not null"`
    PostCategoryID uint32 `gorm:"postcategoryid"`
    // PostCollections []PostCollection `gorm:"many2many:post_collection;"`
    // PostTags        []PostTag        `gorm:"many2many:post_tag; constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}

type PostCategory struct {
    ID            uint32 `gorm:"primarykey"`
    PostCatName   string `gorm:"postcatname;not null"`
    PostCatSlug   string `gorm:"postcatslug;unique;not null"`
    PostCatParent uint32 `gorm:"postcatparent"`
    Posts         []Post `gorm:"foreignKey:PostCategoryID"`
}

var DB *gorm.DB

func main() {
    databaseConfig := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?multiStatements=true&parseTime=true", "root", "", "127.0.0.1", "3306", "tester")

    DB, _ = gorm.Open(mysql.Open(databaseConfig), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    sqlDB, _ := DB.DB()
    defer sqlDB.Close()

    DB.AutoMigrate(&PostCategory{}, &Post{})

    // ============ Function Get ================
    var categories []PostCategory = []PostCategory{
        {
            ID: 1,
        },
        {
            ID: 2,
        },
    }
    var post []Post

    DB.Model(&categories).Association("Posts").Find(&post)

    fmt.Printf("%+v\n", post)
}

Output:

2021/11/23 20:28:41 D:/go/src/udemy-solving/00002/main.go:54
[1.054ms] [rows:3] SELECT * FROM `posts` WHERE id IN (1,2,3) AND `posts`.`post_category_id` IN (1,2) AND `posts`.`deleted_at` IS NULL
[{Model:0xc0001b5380 ID:1 PostTitle:example title 1 PostSlug:ex-1 PostCategoryID:1} {Model:0xc0001b53e0 ID:2 PostTitle:example title 2 PostSlug:ex-2 PostCategoryID:2} {Model:0xc0001b5440 ID:3 PostTitle:example title 3 PostSlug:ex-3 PostCategoryID:1}]

My MySQL Example Data:

user_174880150
Related