GORM many2很多预加载错误

Question

Currently using GORM to connect to two databases: POSTGRES AND sqlite (using a code switch to choose which one to use). I have a 2 database tables defined in my schema that look like this:

type TableClient struct { 
    Model
    Synchronised bool
    FacilityID string `gorm:"primary_key"`
    Age                       int
    ClientSexID               int  
    MaritalStatusID           int
    SpecificNeeds             []TableOptionList`gorm:"many2many:options_specific_needs"`
}

type TableOptionList struct {
    ID        int `gorm:"primary_key"`
    Name    string
    Value   string
    Text    string
    SortKey int
  }

Previously, I would preload related table with code like this:

var dbClient TableClient
Db.Where("facility_id = ? AND client_id = ? AND id = ?;", URLFacilityID, URLClientID, URLIncidentID).
   Preload("ClientSex").
   Preload("MaritalStatus").
   Preload("CareTakerRelationShip").
   Preload("HighestLevelOfEducation").
   Preload("Occupation").
   Preload("SpecificNeeds").
   First(&dbClient)

Now that lookup fails with a syntax error and when I look at the SQL generated, it shows the following SQL is generated:

SELECT * FROM "table_option_lists" INNER JOIN "options_specific_needs" ON       "options_specific_needs"."table_option_list_id" = "table_option_lists"."id" WHERE (("options_specific_needs"."table_client_id","options_specific_needs"."table_client_facility_id") IN (('one','LS034')))

Pasting that into the sqlite console also fails with the same error: (near ",": syntax error)

Answer 1

The crux of your issue is in your WHERE clause, you need to use "OR", not ",".

This is likely an issue with GORM and you could open a GitHub issue with them for this. You can get around the issue using db.Raw() from GORM. In general, I prefer to avoid ORMs. Part of the reason is it is often easier to just build an SQL query by hand than try to figure out the strange way to do it in the ORM (for things beyond basic CRUD).