How to use Raw() for Preload() in Gorm?

php Xiaobian Yuzai will introduce to you how to use Raw() for Preload() in Gorm. Gorm is a powerful Go language ORM library that provides many convenient methods for database operations. In some cases, we may need to use native SQL statements for queries, and also want to use the Preload() method to preload related data. By using the Raw() method, we can combine Preload() in Gorm to achieve this requirement. Next, we will explain the specific steps in detail to help you better understand and apply this technique.

Question content

In my previous 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 this entirely using the stuff/methods in gorm, so I'm just using a simple query that I'm more familiar with. Now, I want to get the records associated with tbl1.type_id. I tried adding Preload() before Raw() but that doesn't seem to work because inspecting the contents of the array of structures I'm using to store the query results doesn't seem to have the Type populated .

renew:

After looking around, I found a way to convert the Raw() query above into a gorm method chain. 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, which is how to get the associated table to table1 (it's actuator_device_infos on the method link, a bit lazy Still cleaning up new code). Even if I add Preload() before the query build method, it doesn't seem to affect the resulting records. I need to use Model() for method chaining, and missing it produces an error because the parsed table in Model() will be used for the FROM of the final query section so gorm should have some idea what I want to preload exists. What I'm preloading is actuator_types, which already has a Joins() as shown in the code above.

Solution

Thanks to @Trock's comment, I finally found the last piece of the puzzle. It seems I just need to use Find() at the end of the method chain so Preload() will work. The final code looks just like the code I had in the question, but I replaced Scan() with Find(). I tried doing the same thing with the Raw() code and it worked fine too. gorm This mechanism should be mentioned.

The above is the detailed content of How to use Raw() for Preload() in Gorm?. For more information, please follow other related articles on the PHP Chinese website!