How to add custom where clause on Relation?

[imraan-go]

Is there any way to add additional condition when using Relation ?

q := repo.db.NewSelect().Model(&product)
q.Relation("Stock", func(q *bun.SelectQuery) *bun.SelectQuery {
			return q.Where("branch_id = ?", filter.BranchId)
		})

This above code should produce something like:

SELECT * from products as product
LEFT JOIN "stock" AS "stock" ON ( "stock"."product_id" = "product"."id" ) 
	AND stock.branch_id = 5

The Where condition should be inside the left join clause. But currently it produces something like:

SELECT * from products as product
LEFT JOIN "stock" AS "stock" ON ( "stock"."product_id" = "product"."id" ) 
WHERE branch_id = 5

Therefore left join does not work. I can do the same with Join() and JoinOn() but then Bun does not populate struct for some reason.

[vmihailenco]

How q.Where("branch_id = ?", filter.BranchId) is better than using q.JoinOn("branch_id = ?", filter.BranchId)?

[fiftin]

If we will implement this we will break current behaviour. @vmihailenco is it ok?

[vmihailenco]

I think we should make this work for has-one and belongs-to relations:

q.Relation("Stock", func(q *bun.SelectQuery) *bun.SelectQuery {
			return q.JoinOn("branch_id = ?", filter.BranchId)
		})

[imraan-go]

.Where("branch_id = ?", filter.BranchId)

I can't seem to make q.JoinOn("branch_id = ?", filter.BranchId) work. Do you have any example that will additional conditional Join statemment? I've tried custom join and joinOn combination like this

q.Join("LEFT JOIN stocks AS stock").
		JoinOn("stock.id = product.id").
		JoinOn("stock.branch_id = ?", filter.BranchId)

But this does not select stock columns at all. Is this a bug?

[jweckschmied]

@imraan-go Did you ever figure this out? I'm having the exact same problem, tried it with Join() and JoinOn() but bun doesn't populate the struct...

[iktakahiro]

same situation here.

Two queries with different WHERE positions have different meanings. Furthermore, JoinOn() seems to be ignored.

IMO, In the current behavior of Bun, I think it is better to use Join() than Relation() in most cases.

[pankrator]

Same situation for me. I need to add a join on clause with Relation (in order to use bun auto populate), but it seems like it doesn't even respect what I have put in the JoinOn clause.

[mantikafasi]

@vmihailenco any updates on implementing this?

I tried to also use raw sql but that time I wasnt able to map joined tables to their own structs.

[tpoxa]

joinOn with has-one Relation causes bun: query has no joins error

[tpoxa]

As a quick solution, I made for my needs it's changing from has-one to has-many even if only one relation record is possible.

type Cluster struct {
  	Servers []*Server `bun:"rel:has-many,join:id=cluster_id"`
}

Then I added Where for the Relation like below:

var entries []*models.Cluster
query := p.db.NewSelect().Model(&entries)

query.Relation("Servers", func(q *bun.SelectQuery) *bun.SelectQuery {
	q.Where("server.workspace_id=?", *f.WorkspaceID) // filter
	return q
})

So I still see all clusters (main entity) and optionally I can see an installation of that cluster (server) but only if this installation is within the current WorkspaceID

Hope this will help someone. Cheers.

[mantikafasi]

joinOn with has-one Relation causes bun: query has no joins error

you should probably first add "join:id=blabla" to bun field

[mantikafasi]

As a quick solution, I made for my needs it's changing from has-one to has-many even if only one relation record is possible.

type Cluster struct {
  	Servers []*Server `bun:"rel:has-many,join:id=cluster_id"`
}

Then I added Where for the Relation like below:

var entries []*models.Cluster
query := p.db.NewSelect().Model(&entries)

query.Relation("Servers", func(q *bun.SelectQuery) *bun.SelectQuery {
	q.Where("server.workspace_id=?", *f.WorkspaceID) // filter
	return q
})

So I still see all clusters (main entity) and optionally I can see an installation of that cluster (server) but only if this installation is within the current WorkspaceID

Hope this will help someone. Cheers.

I actually thought about that too but the problem with that is I want to use sql functions and primitivies like "some_date > now()" or "active=false". bun straight throws errors when I try to

[LeoNdV001]

2 years later and the problem still exists. The current behavior doesn't make sense. If I want the Where in the base query, I will add it to the base query. The code suggests applying conditions on the join.

I would settle for the solution of vmihailenco

the only workaround now is to select all the columns you want to populate structs in a ColumnExpr()

// User represents the database table
type User struct {
	bun.BaseModel `bun:"users"`
	ID        string `bun:"id,pk,type:uuid,default:uuid_generate_v4()" json:"id"`
	CreatedAt string `bun:"created_at" json:"created_at"`
	UpdatedAt string `bun:"updated_at" json:"updated_at"`
}

// Columns returns a slice of columns
func (model *User) Columns() []string {
	return []string{"id", "created_at", updated_at"}
}

// TableInflectedColumns returns a slice of columns with the table name prefix
func (model *User) TableInflectedColumns(table string) []string {
	columns := make([]string, 0)

	for i := range model.Columns() {
		columns = append(columns, fmt.Sprintf("%s.%s AS %s__%s", table, modelColumns[i], table, modelColumns[i]))
	}

	return columns
}

parentTable []models.ParentTable

q.NewSelect().
	Model(&parentTable).
	ColumnExpr(strings.Join((&models.ParentTable{}).TableInflectedColumns("parent_table"), ",").
	ColumnExpr(strings.Join((&models.User{}).TableInflectedColumns("created_by_user"), ",").
	Join("LEFT JOIN public.users AS created_by_user").
	JoinOn("created_by_user.id = x.created_by").
	JoinOn("created_by_user.deleted_at IS NULL").
	Scan(ctx)

[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.