Gorm Selecting specific column when working with has many relation

I want to select a specific column in has many relation table

type Product struct {
    gorm.Model
    Images      []Image `json:"image_id"`
    Category_id int     `json:"category_id" `
    Name        string  `json:"name"`
    Price       int     `json:"price"`
    Quantity    int     `json:"quantity"`
    Specs       string  `json:"specs"`
}
type Image struct {
    gorm.Model
    Product_id uint   `json:"product_id"`
    Photo      string `json:"image_link"`
}
type ProductResp struct {
    ID          uint     `gorm:"primarykey" json:"id"`
    Images      []string `json:"image_id"`
    Category_id int      `json:"category_id" `
    Name        string   `json:"name"`
    Price       int      `json:"price"`
    Quantity    int      `json:"quantity"`
    Specs       string   `json:"specs"`
}

here I have a database model Product. a Product can have many images. when I select products in product table, I want only photo (specific column in image table) field of image struct. how can I select the photo column in images table

Expected answer

how can I select the photo column in images table

Comment From: dkasyanov

Hi,

Looks like Preload is what are you looking for. https://gorm.io/docs/preload.html#Preload

You could try to fetch specific products with their images in the next way:

var products []Product
db.Where("name = ?", "product1").Or("name = ?", "product2").Preload("Images", "photo is not null").Find(&products)

Comment From: nibrasmuhamed

Hi, as you said, I am trying to preload. but, when I am preloading images table, I am getting all fields of images (image id, createdat, deletedat, updatedat, product_id and photos). I don't want all the columns in image table rather than photo column. photo column contains the image link. my question is how to get specific field or column (photo column) ?

Comment From: dkasyanov

You could use Select to pick specific column from from images. It would still return you product_id as it is doing a JOIN operation on the product_id.

You could try this one:

var products []Product
db.Where("name = ?", "product1").Or("name = ?", "product2").Preload("Images", "photo is not null").Select("images.photo").Find(&products)

it translates to SQL query

SELECT products.*, images.photo FROM products LEFT JOIN images ON images.product_id = products.id WHERE (name = 'product1' OR name = 'product2') AND images.photo is not null;

Comment From: nibrasmuhamed

Error 1054 (42S22): Unknown column 'images.photo' in 'field list' [11.383ms] [rows:0] SELECT images.photo FROM products WHERE products.deleted_at IS NULL [127.0.0.1]:52620 200 - GET /user/

I have almost tried everything. last code you've sent isn't working.

I want to select all the products from products table.

type Product struct {
    gorm.Model
    Images      []Image `json:"image_id"`
    Category_id int     `json:"category_id" `
    Name        string  `json:"name" gorm:"unique;not null"`
    Price       int     `json:"price"`
    Quantity    int     `json:"quantity"`
    Specs       string  `json:"specs"`
}
type Image struct {
    gorm.Model
    Product_id uint   `json:"product_id"`
    Photo      string `json:"image_link"`
}

and in Image field, I want every link of image as slice. image link is stored in images table in Photo column.

so I am expecting output like this

[ {
            "id": 2,
            "image_id": [
                "http://localhost:8000/images/1ffdfbbc-a77a-4c1c-a9a6-ab673b7ef5c6.jpg",
                "http://localhost:8000/images/f11b9c7e-1a02-42a5-8e2c-6e63ea22f1f8.jpg",
                "http://localhost:8000/images/39f82eac-9138-4c2e-9946-31f8b07d91d3.jpg"
            ],
            "category_id": 1,
            "name": "Iphone",
            "price": 100000
        },
 {
            "id": 2,
            "image_id": [
                "http://localhost:8000/images/1ffdfbbc-a77a-4c1c-a9a6-ab673b7ef5c6.jpg",
                "http://localhost:8000/images/f11b9c7e-1a02-42a5-8e2c-6e63ea22f1f8.jpg",
                "http://localhost:8000/images/39f82eac-9138-4c2e-9946-31f8b07d91d3.jpg"
            ],
            "category_id": 1,
            "name": "Iphone",
            "price": 100000
        },
]