postgresql 包含软删除行的Join子句

a64a0gku  于 2022-12-03  发布在  PostgreSQL
关注(0)|答案(1)|浏览(126)

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

idfiyjo8

idfiyjo81#

在浏览了一下pg-go的代码之后,我不知道你要做的事情是否被支持。为了确定,你可能想在调试器中单步执行下面的代码。
为联接构建查询时,它包含以下部分:
https://github.com/go-pg/pg/blob/c9ee578a38d6866649072df18a3dbb36ff369747/orm/join.go#L283

if isSoftDelete {
        b = append(b, " AND "...)
        b = j.appendAlias(b)
        b = j.appendSoftDelete(b, q.flags)
    }

j.appendAlias(b)调用下面的appendAlias()函数:https://github.com/go-pg/pg/blob/c9ee578a38d6866649072df18a3dbb36ff369747/orm/join.go#L200

func appendAlias(b []byte, j *join) []byte {
    if j.hasParent() {
        b = appendAlias(b, j.Parent)
        b = append(b, "__"...)
    }
    b = append(b, j.Rel.Field.SQLName...)
    return b
}

由于这两个连接都有一个has-one父关系,因此将为所有表添加该父关系:https://github.com/go-pg/pg/blob/c9ee578a38d6866649072df18a3dbb36ff369747/orm/join.go#L153

func (j *join) hasParent() bool {
    if j.Parent != nil {
        switch j.Parent.Rel.Type {
        case HasOneRelation, BelongsToRelation:
            return true
        }
    }
    return false
}

我认为解决这个问题的方法是只为父关系调用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

func (j *join) appendSoftDelete(b []byte, flags queryFlag) []byte {
    b = append(b, '.')
    b = append(b, j.JoinModel.Table().SoftDeleteField.Column...)
    if hasFlag(flags, deletedFlag) {
        b = append(b, " IS NOT NULL"...)
    } else {
        b = append(b, " IS NULL"...)
    }
    return b
}

相关问题