Golang gorm - Get relation without using preload

I'm new in gorm and golang and I'm working on a query to get conversations of the user with the users that are in the conversation. I have the query working as expected, but my issue is that it does not bring the relation of the users.

This is my conversations model:

type Conversation struct {
    ID       uint   `gorm:"primarykey"`
    Users    []User `gorm:"many2many:user_has_conversations;"`
    Messages []ConversationMessage
}

This is my request to the db:

var conversations []model.Conversation

result := r.db.Debug().Scopes(
        pagination.Paginate,
        scope.OrderByIDDescTable(conversationsTable),
    ).Joins(
        "INNER JOIN `user_has_conversations` ON `user_has_conversations`.`conversation_id` = `conversations`.`id`",
    ).Joins(
        "INNER JOIN `users` ON `users`.`id` = `user_has_conversations`.`user_id` AND `users`.`id` = ?",
        UserID,
    ).Find(&conversations)

This query work's fine and brings the conversation of the users correctly, but does not give me back the users in the relation and when you look at the query that it runs does not bring the users field.

The query that executes:

SELECT `conversations`.`id` 
FROM `conversations` 
INNER JOIN `user_has_conversations` ON `user_has_conversations`.`conversation_id` = `conversations`.`id` 
INNER JOIN `users` ON `users`.`id` = `user_has_conversations`.`user_id` AND `users`.`id` = 1 
ORDER BY `conversations`.`id` desc LIMIT 10;

In the query you can see that the query is correct, but does not have the Select with the fields of the relation.

If I use preload also with the inner join does give me the right solution, but I don't think it's good for performance when I have already the join to do preload of the users to get the users again. I would like to be able to get it with the join and not also use the preload.

This example works, but I would like to use it without the preload if there is a way.

result := r.db.Debug().Scopes(
        pagination.Paginate,
        scope.OrderByIDDescTable(conversationsTable),
    ).Preload("Users").Joins(
        "INNER JOIN `user_has_conversations` ON `user_has_conversations`.`conversation_id` = `conversations`.`id`",
    ).Joins(
        "INNER JOIN `users` ON `users`.`id` = `user_has_conversations`.`user_id` AND `users`.`id` = ?",
        UserID,
    ).Find(&conversations)

Solution

You can do that by implementing customized data type for gorm and use it in your query to receive the users slice from the database (coming below).

The customized data type has to implement the Scanner and Valuer interfaces, so GORM knows how to receive/save it into the database. https://gorm.io/docs/data_types.html

type Users []User

func (u Users) Value() (driver.Value, error) {
    return json.Marshal(u)
}

func (u *Users) Scan(src interface{}) error {
    if bytes, ok := src.([]byte); ok {
        return json.Unmarshal(bytes, u)
    }
    return errors.New(fmt.Sprint("Failed to unmarshal JSON from DB", src))
}

so your conversation type should be updated like below:

type Conversation struct {
    ID       uint  `gorm:"primarykey"`
    Users    Users `gorm:"->"`
    Messages []ConversationMessage
}

to be able to query all participated users in a conversation we need to build the json object by using jsonb_agg and jsonb_build_object

var conversations []model.Conversation

result := r.db.Debug().Scopes(
        pagination.Paginate,
        scope.OrderByIDDescTable(conversationsTable),
    ).
    Select(conversationsTable + ".*," +
    "jsonb_agg(
        distinct jsonb_build_object(
            'id',   u.id,
            'name', u.name,
            //...
        )
    ) as users").
    Joins("INNER JOIN `user_has_conversations` ON `user_has_conversations`.`conversation_id` = `conversations`.`id`").
    Joins("INNER JOIN `users` as u ON `u`.`id` = `user_has_conversations`.`user_id` AND `u`.`id` = ?", UserID).
    Find(&conversations)