Previously on my project, I needed to do some complex queries so I used Raw(). The query looks like this:
SELECT tbl1.id, tbl1.some_name, tbl5.some_status, tbl1.some_tbl2_id, tbl1.type_id, tbl1.created_at, tbl5.name
FROM table1 tbl1
JOIN table2 tbl2 ON tbl1.some_tbl2_id = tbl2.id
JOIN table3 tbl3 ON tbl3.edge_device_info_id = tbl2.id
JOIN table4 tbl4 ON tbl4.id = tbl3.account_id
LEFT JOIN table5 tbl5 ON tbl5.tbl1_id = tbl1.id
WHERE tbl5.tbl1_id IS NULL OR tbl5.updated_at = (
SELECT MAX(updated_at)
FROM table5
WHERE tbl1_id = tbl1.id
)
ORDER BY tbl1.created_at ASC;
I'm not quite sure if I can do that completely with the stuff/methods from gorm so I just went with what I'm more familiar with which is just plain queries. Now, I want to get a the record associated with tbl1.type_id. I tried adding a Preload() before Raw() but that doesn't seem to work as checking the contents of the array of structs I made to store the result of the query doesn't seem to have a filled up Type.
UPDATE:
After looking around, I found a way to convert the Raw() query above to gorm's method chaining. And it looks like this:
Model(models.ActuatorDeviceInfo{}).Preload("ActuatorDeviceInfo.Type").
Select("actuator_device_infos.*, actuator_device_infos.id, at.*").
Joins("JOIN edge_device_infos edi ON actuator_device_infos.parent_edge_device_id = edi.id").
Joins("JOIN user_owned_edge_devices ae ON ae.edge_device_info_id = edi.id").
Joins("JOIN accounts acc ON acc.id = ae.account_id").
Joins("JOIN actuator_types at ON at.id = actuator_device_infos.type_id").
Joins("LEFT JOIN actuator_updates au ON au.actuator_device_info_id = actuator_device_infos.id").
Where("au.actuator_device_info_id IS NULL OR au.updated_at = (?)",
helpers.GetDB().Model(&models.ActuatorUpdate{}).Select("MAX(updated_at)").
Where("au.actuator_device_info_id = actuator_device_infos.id")).
Order("actuator_device_infos.created_at DESC").
Scan(&actuator_device_infos)
It works just like the previous Raw() query, but it's still missing something, and that is the way to get an associated table to table1 (it is actuator_device_infos on the method chaining, kinda lazy to still clean the new code). Even if I add a Preload() before the query building methods, it doesn't seem to affect the resulting records. And I needed to use Model() for the method chaining as well as it's absence would create an error as the resolved table from Model() would be used on the FROM part of the final query, so gorm should be somewhat aware that the thing I want to preload exist. The thing I preload is actuator_types, and it already have a Joins() for it, as you can see in the code above.
Thanks to @Trock's comment, I finally found the last piece of the puzzle. It seems like I just need to use Find() at the end of the method chain so Preload() would work. The final code looks just like the code I have on my question but I replaced the Scan() with Find(). And I tried to do the same for the Raw() code, and it worked as well. gorm should've mentioned that mechanism.