Join clause including soft-deleted rows

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

Solution

After looking through the code of pg-go a bit, I don’t know if what you’re trying to do is supported. To know for sure you’d probably want to step through the code below in a debugger.

When the query is being built for the joins, it has this section:

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)
    }

The line j.appendAlias(b) calls the appendAlias() function below: 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
}

Since the joins both have a has-one parent relation, it gets added for all the tables: 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
}

I think what would fix this for you would be to only call appendAlias() for the parent relation and not the other two, but it doesn’t look like that’s supported by pg-go.

What you can do for this is just call pg.Query() or pg.QueryWithContext() and pass in the sql statement you included above.

It’s also worth mentioning that pg-go/pg is in maintenance mode so it’s unlikely they’ll ever support this. Depending on how entrenched this project is in pg-go, you might consider using Bun which is actively being developed.

Appendix

Here’s the appendSoftDelete() function that gets called in the first snippet above:

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
}