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