What will be the best way to filter on a "preloaded table"?

[isgj]

Currently when you preload a table (left join), it will alias the related table to {table_name}_{random_number}.
The function accepts ...{dialect}.PreloadOption. This makes it hard to filter on the related table.

Can the alias be predictable or configurable?
Or can the mods implement the PreloadOption interface so they can applied there too (or a different signature for Preload...())?

[stephenafamo]

The reason for adding the random number is to prevent accidentally messing around with the actual query the user wants to run.

And when we consider nested preloading, (i.e. post -> preload author {users table} -> preload organization -> preload owner {users table}), the random numbers help prevent collisions.

Furthermore, if we look at it critically, the only things that can affect the preload only without affecting the rest of the query is the columns preloaded, and any nested loader. This is why these are the only things the options can modify.

To filter by the related table, we can do a regular join to the new table and filter appropriately

models.Posts(
  models.PreloadPostAuthor(),

  // Add your own join and filter
  models.UsersWhere.ID.EQ(10),
  models.SelectJoin(ctx).Posts.InnerJoin.Users,
)

Most database systems would be able to optimize this query quite well

[stephenafamo]

This makes sense. Let me see if I can get a quick implementation

[stephenafamo]

I've added PreloadWhere on the main branch 470d060. I takes a function which will be passed the table aliases being used for preloading and should return a slice of expressions that will be added to the ON clause.

An example of using it will look like this:

	models.Users(ctx, db,
		models.PreloadUserTo(psql.PreloadWhere(func(from, to string) []bob.Expression {
			return []bob.Expression{
				psql.Quote(from, models.ColumnsNames.Users.Verified).EQ(true),
			}
		})),
	)

The reason it is necessary to pass from and to is because the preload table name may be different from the base table name.

Sidenote: For consistency, OnlyColumns and ExceptColumns have also be renamed to PreloadOnly and PreloadExcept

[hiendaovinh]

Fantastic!
I love bob because you're so responsive to questions and suggestions.

[stephenafamo]

Heads up.

I just realized that this would become messy for multi-sided relationships, for example a user-configured "related through".
This is because these extra clauses will be applied to all sides which is likely not what the user wants.

I'll make a few modifications to PreloadWhere to account for this.

[stephenafamo]

Done (8104434)

Now instead of a single function PreloadWhere takes a variadic number of functions and will match them to each side of the relationship to load.

So for a relationship with 3 sides, the first function passed to PreloadWhere will only be applied to the first side, the 2nd function to the 2nd side and the 3rd function to the 3rd side.

I'll need to add a bit of documentation before releasing this so people don't get confused.