Fetching a struct and other data in one query

user_133747 0
Viewed 500

I have the following table schema:

user
-----
id uuid
name string

user_model
------
id uuid
user_id uuid
model_id uuid
role int

model
_____
id uuid
name string
model_no string

I have the following code which fetches the data from the "model" table.

underlyingModel = &model{}
var model IModel
model = underlyingModel
role := 0

db.Table("model").Joins('INNER JOIN user_model ON user.id = user_model.uuid')
.Joins('INNER JOIN model ON user.id = model_id').Find(&model);

In my actual code, the model can be many different struct types with different fields, they're all behind the IModel interface.

What I want to do is to fetch that extra role field from the user_model in one query. Something like .Find(&model, &role).

Is it possible using Gorm?

go-gorm go postgresql orm database
1 Answers

One possible solution is to create an anonymous struct to put the results in, with a combination of the Select() method.

var selectModel struct {
   ID string //I'm assuming uuid matches the string
   Name string
   ModelNo string
   Role int
}

db.Table("model").
   Joins("INNER JOIN user_model ON user.id = user_model.uuid").
   Joins("INNER JOIN model ON user.id = model_id").
   Select("model.id, model.name, model.model_no, user_model.role").
   Find(&selectModel);

Basically, you create an anonymous struct with selectModel variable, containing all the fields you want to return. Then, you need to do a select statement because you need some fields that are not part of the model table.

Here you can find more info on Smart Select Fields in form.

EDIT:

Based on additional info from the comments, there is a solution that might work.

Your IModel interface could have two methods in its signature, one to extract a string for the SELECT part of the SQL query, and the other one to get a pointer of the selectModel that you would use in the Find method.

type IModel interface {
  SelectFields() string
  GetSelectModel() interface{}
}

The implementation would go something like this:

func (m *model) SelectFields() string {
  return "model.id, model.name, model.model_no, user_model.role"
}

func (m *model) GetSelectModel() interface{} {
  return &m.selectModel
}

type model struct {
 selectModel
 ID uint64
 Age int
}


type selectModel struct {
  Name string
  Email string
}

Then, your query could look something like this:

   var m IModel
   m = model{}

   db.Table("model").
   Joins("INNER JOIN user_model ON user.id = user_model.uuid").
   Joins("INNER JOIN model ON user.id = model_id").
   Select(m.GetSelectFields()).
   Find(m.GetSelectModel());
user_75675790
Related