Allow join definitions using fields from relations

[tbe]

Joining currently only works of both tables have a common field to join.

For example using the pg_catalog:

type pgClass struct {
	bun.BaseModel `bun:"table:pg_catalog.pg_class,alias:c"`

	OID          int64 `bun:",pk"`
	Relname      string
	Relnamespace int64
	Reltuples    float64
	Relpages     int64
	Reloptions   []string `bun:",array"`
	Relkind      string   `bun:",type:char"`

	Namespace *pgNamespace `bun:"rel:has-one,join:relnamespace=oid"`
}

type pgNamespace struct {
	bun.BaseModel `bun:"table:pg_namespace,alias:ns"`

	OID     int64 `bun:",pk"`
	Nspname string
}

type pgAttribute struct {
	bun.BaseModel `bun:"table:pg_catalog.pg_attribute,alias:att"`

	Attrelid     int64 `bun:",pk"`
	Attnum       int   `bun:",pk"`
	Attname      string
	Atttypid     int64
	Attisdropped bool

	Class *pgClass `bun:"rel:has-one,join:attrelid=oid"`
	Stats *pgStats `bun:"rel:has-one,join:attname=attname"` // <<-- HERE
}

type pgStats struct {
	bun.BaseModel `bun:"table:pg_catalog.pg_stats"`

	Schemaname string `bun:",pk"`
	Tablename  string `bun:",pk"`
	Attname    string `bun:",pk"`
}

As pg_stats has no OID, pgClass.Relname and pgNamespace.Nspname would be required to join.

While Table.FieldMap has entries for the columns of the relations (for example class__namespace__nspname), and Table.hasOneRelation excepts these as column names in the join clause, the query will contain exactly these names, and of course will fail.

I also found no way to define Stats *pgStats as a relation but provide the join condition at the query itself. The only way that seems to work is providing an additional WHERE condition. But this is ugly and error-prone.

Using fields from dependent relations (for example join:c.relname=tablename) would IMHO be the logical way to do this.

[nilsocket-w1]

I also need this feature.

Ability to have separate join conditions for each table which allows individual columns would be really great.

[e200]

+1

[e200]

This is a hack, but works

Relation("Meta", func(sq *bun.SelectQuery) *bun.SelectQuery {
	return sq.
		ColumnExpr("my_votes.vote as meta__my_vote").
		ColumnExpr("meta.views_count as meta__views_count").
		ColumnExpr("meta.answers_count as meta__answers_count").
		ColumnExpr("meta.votes_count as meta__votes_count").
		ColumnExpr("meta.post_id as meta__post_id")
}).
Join("LEFT JOIN post_votes AS my_votes").
JoinOn(
	"my_votes.post_id = question.id AND my_votes.user_id = ?",
	userID,
).
Group("post.id", "my_votes.vote", "user.id", "meta.id").

It's not the best approach but can give you some time til support is added.

[github-actions]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.

[e200]

Still an issue.

[j2gg0s]

I’m not very familiar with Bun’s relationship definitions, so I’d like to double-check.
The query we want:

SELECT *
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_stats s ON s.schemaname = n.nspname AND s.tablename = c.relname AND s.attname = a.attname
WHERE
    c.relname = 'pg_class';

Under a model deifition like this:

type pgAttribute struct {
   Stats *pgStats `bun:"rel:has-one,join:attname=attname,attrelid=pg_class.oid,pg_class.relnamespace=pg_namespace.oid"`
}

[github-actions]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.