Join clause with soft deleted rows

Question content

I am having trouble generating a correct join select query using go-pg orm, one of the tables The record can be soft deleted, but the other 2 table records cannot.

Database Table:

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 Model:

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 try 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

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

Out of all 3 tables/models mentioned above, only the pipeline_triggers table has the deleted_at column for soft deletes. My requirement is to include the soft deleted pipeline_triggers rows in the result set as well. But go-pg orm will automatically add trigger.deleted_at is null condition in the join clause. How can I remove this condition and get all rows including soft deleted rows.

I tried using the allwithdeleted function, but it works for the main model, which is pipeline_trigger_events (and the table doesn't have a deleted_at column anyway), but not for pipeline_triggers , so it fails with this error: pg: model=pipelinetriggerevent does not support soft deletion

Correct answer

After browsing the code of pg-go for a while, I don’t know if Support what you want to do. To determine this, you may need to step through the code below in the debugger.

When building a query for a join, it has the following parts:

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

j.appendalias(b) line calls the following appendalias() function: 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 joins all have a one-to-one parent relationship, it is added for all 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 thought the way around this would be to call appendalias() only for the parent relationship and not the other two, but it looks like pg-go doesn't support this.

To do this, you simply call pg.query() or pg.querywithcontext() and pass in the sql statement contained above.

It's also worth mentioning that pg-go/pg is in maintenance mode, so they are unlikely to support this. Depending on how entrenched this project is in pg-go, you might consider using bun which is under active development.

appendix

This is the appendsoftdelete() function called in the first code 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
}

The above is the detailed content of Join clause with soft deleted rows. For more information, please follow other related articles on the PHP Chinese website!