Deferred eager loading of relations

[yoh]

Hi,
Is there a way to load (preload / eager load) relations after the initial query?

My use case is the following:

1. I first fetch some records from the database.
2. I run some business logic on them.
3. Then, based on the result of that logic, I would like to load relations conditionally.

So the relations are not known at query time, but only after I’ve processed the main dataset.

I’m using the dvdrental sample database for my tests.
https://neon.com/postgresql/postgresql-getting-started/postgresql-sample-database

Thanks!

// first I load customers from db
ctx := c.Request().Context()
var customers []models.Customer
err := db.NewSelect().
	Model(&customers).
	Where("customer_id > ?", 10).
	// Relation("Address.City.Country"). // preload with JOIN
	// Relation("Payments.Rental"). // preload with WHERE IN
	OrderBy("customer_id", bun.OrderAsc).
	Limit(10).
	Scan(ctx)
if err != nil {
	...
}

... lot of code ...

// in some case, if something, I want to preload relations
if something {
	db.???(&customers).
		Relation("Address.City.Country"). // may preload with WHERE IN + JOIN (or only WHERE IN)
		Relation("Payments.Rental"). // preload with WHERE IN
		Scan(ctx)
}


// I can make this but it will reload the customers... :\
err := db.NewSelect().
	Model(&customers).
	Where("customer_id IN (?)", bun.In(customerIds)).
	Relation("Address.City.Country"). // preload with JOIN
	Relation("Payments.Rental"). // preload with WHERE IN
	Scan(ctx)

[NathanBaulch]

This is possible using .ExcludeColumn("*").WherePK() in your second "deferred" query. Relations will be attached to your existing slice of model structs thanks to WherePK() and none of the existing data will be fetched again thanks to ExcludeColumns("*"). You don't need to manually build a customer_id IN where condition in your example. Just keep in mind that the IN clause will grow with the number of records selected, so be careful how many you fetch.

[yoh]

Thanks for the suggestion @NathanBaulch !

I tried your solution, but it seems Bun generates an intermediate query (SELECT with no columns) when using ExcludeColumn("*") + WherePK() + Relation(...).

It works, but the intermediate query feels wrong.
Here are the 3 queries generated:

[bun]  22:09:06.268   SELECT   2.046ms  SELECT "customer"."customer_id", ... ORDER BY "customer_id" ASC LIMIT 10
[bun]  22:09:06.269   SELECT   1.286ms  SELECT FROM "customer" WHERE "customer"."customer_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
[bun]  22:09:06.273   SELECT   3.97ms   SELECT "payment"."payment_id", ... WHERE ("payment"."customer_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

And the code used:

ctx := c.Request().Context()
var customers []models.Customer
err := db.NewSelect().
	Model(&customers).
	Relation("Address.City.Country").
	// Relation("Payments.Rental").
	OrderBy("customer_id", bun.OrderAsc).
	Limit(10).
	Scan(ctx)

// in some case, if something, I want to preload relations
err = db.NewSelect().
	Model(&customers).
	ExcludeColumn("*").
	WherePK().
	Relation("Payments.Rental").
	Scan(ctx)

Is there a cleaner way to achieve this?
Something like db.Preload(&customers).Relation(...).Scan(ctx) that would work directly from already loaded models, without hitting the database again for the main table?

Thanks!

[NathanBaulch]

It looks like Bun only does that superfluous intermediate query when selecting a 1:m relation (eg Payments), not when selecting only m:1 relations (eg Address). I have no idea if this is an oversight or an important implementation detail but I suspect it's unavoidable at this time. If the "SELECT FROM" bothers you then you can change ExcludeColumn("*") to Column("id") and just accept this tradeoff for the convenience of having Bun fill 1:m relations lazily.