-
Couldn't load subscription status.
- Fork 959
help:How can this SQL statement be changed to an SQLC statement? #1572
-
if len(roleApis) > 0 { db := db.Orm.Debug().Model(&models.Api{}). Select("system_api.id"). Joins("left join system_menu_api on system_menu_api.api = system_api.id") for _, p := range roleApis { db = db.Or("system_api.url = ? and system_api.method = ?", p[1], p[2]) } err = db.Where("system_menu_api.menu = ?", menuId).Pluck("system_api.id", &apis).Error if err != nil { response.Error(c, err, response.GetApiError) return } }
Beta Was this translation helpful? Give feedback.
All reactions
-
🚀 1
Replies: 1 comment 1 reply
-
The tricky part is this section:
for _, p := range roleApis { db = db.Or("system_api.url = ? and system_api.method = ?", p[1], p[2]) }
Because you are dynamically adding more SQL to the query based on how many roleApis there are. I think there is a healthy discussion here on that topic.
I'm not sure if sqlc will support this, but I think transforming that query from a dynamic OR into a static one and using an array parameter would probably solve it. This would be an array of tuples, so I kind of have a feeling that will not be supported by SQLC :(
select system_api.id from system_api left join system_menu_api on ( system_menu_api.api = system_api.id OR (system_api.url, system_api.method) = ANY(?) ) where system_menu_api.menu = ?
Beta Was this translation helpful? Give feedback.
All reactions
-
select
system_api.id
from system_api
left join system_menu_api on (
system_menu_api.api = system_api.id
OR (system_api.url, system_api.method) = ANY(?)
)
where system_menu_api.menu = ?
seems that it doesn't work for sqlc v1.22.0. Do you have other solutions?
Beta Was this translation helpful? Give feedback.