I am having trouble generating correct select query with joins using go-pg
orm where one table record can be soft-deleted and other 2 cant.
DB tables:
| pipeline_instances |
| ------------ |
| instance_id int |
| pipeline_id int |
| event_id int |
| pipeline_triggers |
| ------------ |
| id int |
| pipeline_id int |
| deleted_at timestamp |
| pipeline_trigger_events |
| ------------ |
| event_id int |
| trigger_id int |
go-pg Models:
type pipelineTriggerEvent struct {
tableName struct{} `pg:"pipeline_trigger_events,alias:pte"`
Trigger *pipelineTrigger `pg:"rel:has-one,join_fk:id"`
PipelineInstance *pipelineInstance `pg:"rel:has-one,join_fk:event_id"`
*TriggerEvent
}
type pipelineTrigger struct {
tableName struct{} `pg:"pipeline_triggers,alias:pt"`
*Trigger
}
type pipelineInstance struct {
tableName struct{} `pg:"pipeline_pipeline_instances,alias:ppi"`
*PipelineInstance
}
The query I am trying to generate:
SELECT
pte.*, trigger.*, pipeline_instance.*
FROM
pipeline_trigger_events AS pte
LEFT JOIN pipeline_triggers AS trigger ON (trigger.id = pte.trigger_id)
LEFT JOIN pipeline_pipeline_instances AS pipeline_instance ON pipeline_instance.event_id = pte.event_id AND trigger.pipeline_id = pipeline_instance.pipeline_id
The query getting generated by go-pg orm:
SELECT
pte.*, trigger.*, pipeline_instance.*
FROM
pipeline_trigger_events AS pte
LEFT JOIN pipeline_triggers AS trigger ON (trigger.id = pte.trigger_id)
AND trigger.deleted_at IS NULL -- this is the unwanted line.
LEFT JOIN pipeline_pipeline_instances AS pipeline_instance ON pipeline_instance.event_id = pte.event_id AND trigger.pipeline_id = pipeline_instance.pipeline_id
var triggerevents []pipelineTriggerEvent
q := db.Model(&triggerevents).
Column("pte.*").
Relation("Trigger").
Relation("PipelineInstance", func(q *orm.Query) (*orm.Query, error) {
q = q.Join(" AND trigger.pipeline_id = pipeline_instance.pipeline_id")
return q, nil
})
Of all the 3 tables/models mentioned above, only pipeline_triggers table has deleted_at
column that is used for soft deletion. My requirement is to include the soft deleted pipeline_triggers rows also in the result set. But go-pg
orm is automatically adding the trigger.deleted_at IS NULL
condition in the join
clause. How can I remove this condition and get all rows including soft deleted ones.
I tried using AllWithDeleted function but it works on the main model, which is pipeline_trigger_events (and this table does not have deleted_at column anyway) and not on pipeline_triggers
and therefore fails with this error: pg: model=PipelineTriggerEvent does not support soft deletes
1条答案
按热度按时间idfiyjo81#
在浏览了一下pg-go的代码之后,我不知道你要做的事情是否被支持。为了确定,你可能想在调试器中单步执行下面的代码。
为联接构建查询时,它包含以下部分:
https://github.com/go-pg/pg/blob/c9ee578a38d6866649072df18a3dbb36ff369747/orm/join.go#L283
行
j.appendAlias(b)
调用下面的appendAlias()
函数:https://github.com/go-pg/pg/blob/c9ee578a38d6866649072df18a3dbb36ff369747/orm/join.go#L200由于这两个连接都有一个has-one父关系,因此将为所有表添加该父关系:https://github.com/go-pg/pg/blob/c9ee578a38d6866649072df18a3dbb36ff369747/orm/join.go#L153
我认为解决这个问题的方法是只为父关系调用
appendAlias()
,而不为其他两个调用,但看起来pg-go不支持这一点。对此,您可以做的就是调用
pg.Query()
或pg.QueryWithContext()
,并传入上面包含的sql语句。另外值得一提的是,pg-go/pg处于维护模式,所以他们不太可能支持这个项目。根据这个项目在pg-go中的地位,你可能会考虑使用正在积极开发的Bun。
附录
下面是上面第一个代码片段中调用的
appendSoftDelete()
函数:https://github.com/go-pg/pg/blob/c9ee578a38d6866649072df18a3dbb36ff369747/orm/join.go#L189