Join clause with soft deleted rows-Golang-php.cn

包含软删除行的 Join 子句

Question content

I am having trouble generating a correct join select query usinggo-pgorm, 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 }

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

Out of all 3 tables/models mentioned above, only the pipeline_triggers table has thedeleted_atcolumn for soft deletes. My requirement is to include the soft deleted pipeline_triggers rows in the result set as well. Butgo-pgorm will automatically addtrigger.deleted_at is nullcondition in thejoinclause. 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 forpipeline_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) }

Copy after login

The

j.appendalias(b)line calls the followingappendalias()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 }

Copy after login

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 }

Copy after login

I thought the way around this would be to callappendalias()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 callpg.query()orpg.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 usingbunwhich is under active development.

appendix

This is theappendsoftdelete()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 }

Copy after login

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!