Gorm and many to many relations-CodePudding

I have an issue understanding how to fetch related information using gorm. This is the first time ever I'm using an ORM actively.

I'm trying to get all devices that a specific user id has access to. With the current function I get all devices, not only the device belonging to the actual user id, and it also includes the list of users that the device belongs to, which is information i really don't need at this point.

Is there a way to Get the devices that the user has access to using the user_devices join table using gorm, or is this something i should create a custom query in order to achieve what I want?

Thank you.

type User struct {
    ID          uint
    Name        string
    Email       string
    Age         uint8
    Birthday    time.Time
    Password    string
    ActivatedAt time.Time
    OrgID       uint
    Org         Org
    Devices     []Device `gorm:"many2many:user_devices"`
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

type Device struct {
    ID           uint
    Name         string
    Hwaddr       string
    OrgID        uint
    PublicIP     uint
    Org          Org
    Users        []User `gorm:"many2many:user_devices"`
    DeviceType   string
    Identity     string
    LastPolledAt time.Time
    CreatedAt    time.Time
    UpdatedAt    time.Time
}

func (u *Device) FindAllDevicesByUid(db *gorm.DB, user *User) (*[]Device, error) {
    var err error
    devices := []Device{}
    err = db.Debug().Preload("Users", "user_id = ?", user.ID).Find(&devices).Error
    if err != nil {
        return &[]Device{}, err
    }
    return &devices, err
}

Result:

  {
    "ID": 4,
    "Name": "Test 4",
    "Hwaddr": "00:00:00:00:00:04",
    "OrgID": 1,
    "PublicIP": 0,
    "Org": {
      "Id": 0,
      "Name": "",
      "Users": null,
      "CreatedAt": "0001-01-01T00:00:00Z",
      "UpdatedAt": "0001-01-01T00:00:00Z"
    },
    "Users": [
      {
        "ID": 1,
        "Name": "Jan Astrup",
        "Email": "[email protected]",
        "Age": 0,
        "Birthday": "0001-01-01T00:00:00Z",
        "ActivatedAt": "2022-04-24T15:48:40 02:00",
        "OrgID": 1,
        "Org": {
          "Id": 0,
          "Name": "",
          "Users": null,
          "CreatedAt": "0001-01-01T00:00:00Z",
          "UpdatedAt": "0001-01-01T00:00:00Z"
        },
        "Devices": null,
        "CreatedAt": "2022-04-24T15:48:40 02:00",
        "UpdatedAt": "2022-04-24T15:48:40 02:00"
      }
    ],
    "DeviceType": "Router",
    "Identity": "M1923 4",
    "LastPolledAt": "0001-01-01T00:00:00Z",
    "CreatedAt": "2022-04-24T15:17:30 02:00",
    "UpdatedAt": "2022-04-24T15:17:30 02:00"
  }

CodePudding user response：

To load just the devices for a specific user, you can use the Joins function to join tables user_devices and devices.

devices := []Device{}
err = db.Debug().Joins("JOIN user_devices ud ON ud.device_id = devices.id").Where("ud.user_id = ?", user.ID).Find(&devices).Error

Apart from this, you can always load the user and preload its devices:

u := User{}
err = db.Debug().Preload("Devices").First(&u, user.ID).Error

Then, you can use u.Devices to return the user's devices, but I think this approach executes two queries to get the user's devices.