PreloadWhere Documentation/Examples

[daddz]

I am trying to figure out how I can use the generated code to filter a select based on a value in a related to-one table.

While digging through the repository I found the PreloadWhere option which looks like it could be used but unfortunately it's not really documented and there are no examples on how to use it.

Could you show/add some documentation/examples on how to use it with generated SelectWheres?

[stephenafamo]

The best way to do this at the moment is to do a fresh join and then filter as you would:

	videos, err := models.Videos.Query(ctx, db,
		models.PreloadVideoUser(),
		models.SelectJoins(ctx).Videos.InnerJoin.User,
		models.SelectWhere.Users.ID.GT(200),
	).One()

The generated query will has 2 joins, but the DB is smart enough to do the work just once. You can verify this using EXPLAIN ANALYZE

---

Why can't PreloadX take WhereMods?

The reason is that to preload, a random suffix is added to the name of the table. This isolates it from the rest of the query and makes it extremely unlikely that it conflicts with any other part of the query.

So, if you wanted to filter using the same join that was generated for preloading, then we would need to pass the generated table name back to the where mod.
I could not find a good way to do this that did not ruin the user experience.

[daddz]

Ah no that's not what I meant, I have that already: daddz@f546eab#diff-e8a6434756d2e9bc524e5ab19bb5a585f162c46d953e468186e9fc851e96f08dR71

I was referring to the second part of the idea regarding making it convenient to use with the autogenerated code - namely SelectWhere and ColumnNames.

Do you have an idea on how a design could look like and where it would make sense to add (e.g. in the template, expression or mod)?
Currently it would look like this:

...
sm.Where(psql.Quote("uploaded_by", models.ColumnNames.User.Name).EQ(psql.Arg("john"))),
sm.OrderBy(psql.Quote("edited_by", models.ColumnNames.User.ID).Desc()).All()

Whereas something like this would be more convenient:

...
models.SelectWhere.Users.As("uploaded_by").Name.EQ("john"),
sm.OrderBy(models.ColumnNames.Users.As("edited_by").ID).Desc())

Or maybe something in this direction:

uploadedBy := models.PreloadVideoUploadedByUser(psql.PreloadAs("uploaded_by"))
editedBy := models.PreloadVideoCreatedByUser(psql.PreloadAs("edited_by"))

models.Videos.Query(ctx, db,
  uploadedBy,
  editedBy,
  models.SelectWhere.Users.As(uploadedBy).Name.EQ("john),
  sm.OrderBy(models.ColumnNames.Users.As(editedBy).ID).Desc()).All()

In this case I was thinking about an .As() method on the models.SelectWhere.Users and models.ColumnNames.Users but not sure if that would make sense or there is a prettier way.

[stephenafamo]

I can take a look at that part.

[stephenafamo]

I created a PR in #221

I decided to name the method AliasedAs(). While this is more verbose, it properly communicates what is happening and has a much less chance of conflicting with a column name (which will lead to compile errors).

[stephenafamo]

I've also added the AliasedAs() method to the generated Join mods in #222

[daddz]

So far I tried the changes from #221 and it's really nice! Makes the usability a lot smoother, thank you!

[daddz]

Ah sorry, should have opened a discussion instead of an issue.

I played around with PreloadWhere a bit more and it doesn't seem to be what I would need in this case. It adds the expressions to the join condition.

I tried to put what I want to achieve in an example query:

SELECT 
  "foo"."id" AS "id",
  "bar_1234"."id" AS "bar_1234.id"
  "bar_1234"."foo_id" AS "bar_1234.foo_id"
FROM "foo" AS "foo"
  LEFT JOIN "bar" as "bar_1234" 
    ON ("foo"."id" = "bar_1234"."foo_id") --- up until here is what comes out of models.PreloadFooBar()
WHERE
  "bar_1234"."field_i_want_to_filter_on" = 1 --- this is what I'd like to add

[stephenafamo]

It should work like this:

	foos, err := models.Foos.Query(ctx, db,
		models.PreloadFooBar(),
		models.SelectJoins(ctx).Foos.LeftJoin.Bar,
		models.SelectWhere.Bar.Field.EQ("value"),
	).One()

This should both preload the bar for every returned foo AND return only foos that match the condition.