Allow QueryMods in eager loads

[jseriff]

We have a few cases where it would be helpful to use QueryMods during an eager load. An example of how I see that working is something like this:

models.Parent(db,
  qm.LoadM("Children", 
    qm.Where("age >= 18"),
    qm.OrderBy("name"),
  ),
).All()

This would load all of the parents, and their R.Children would contain only children over the age of 17, ordered by their names.

[aarondl]

How would this work with nested eager loading? I think if we're going to go down this road (which previously it was thought people would simply craft their own queries), we should consider nested as well.

[jseriff]

Good question - maybe it doesnt (not a great answer), or maybe it always effects the lowest level child? So, if you wanted to filter both the middle level and bottom level, you would do something like:

models.Parent(db,
  qm.LoadM("Children", 
    qm.Where("age >= 18"),
    qm.OrderBy("name"),
    qm.LoadM("Grandchildren",
      qm.Where("age < 3"),
  ),
).All()

Or if you only wanted to effect the lowest level, you could do:

models.Parent(db,
  qm.LoadM("Children.Grandchildren", 
    qm.Where("age < 3"),
  ),
).All()

Our use case here, is we pass around our "Parent" structure, and do things to its "Children" - by accessing those on the R struct. If we are loading a large number of parents at once, eager loading is definitely the ideal approach. Sometimes however we want to restrict which set of "Children" are being operated on - such as recent ones, or non deleted ones, etc.

[aarondl]

Hey @jseriff, haven't forgotten about this issue. Just been busy.

I've got a proposal for you:

func LoadM(load string, mods ...[]QueryMod) (elided) {}

This would allow:

models.Parent(db,
  qm.LoadM("Children.Grandchildren",
    []qm.QueryMod{qm.Where("age < 3")},
    []qm.QueryMod{qm.Where("age > 4")},
  ),
)

We could syntactic sugar it a bit like so too:

models.Parent(db,
  qm.LoadM("Children.Grandchildren",
    qm.Mods{qm.Where("age < 3")},
    qm.Mods{qm.Where("age > 4")},
  ),
)

This would generate queries like:

select * from parents;
select * from children where (parent_id in (1,2)) AND (age < 3);
select * from grandchildren where (child_id in (3,4,5,6)) AND (age > 4);

[jseriff]

This proposal seems good to me. My only question would how the behavior might work in this case:

models.Parent(db,
  qm.LoadM("Children.Grandchildren",
    []qm.QueryMod{qm.Where("age < 3")},
    []qm.QueryMod{qm.Where("age > 4")},
  ),
  qm.LoadM("Children.Others",
    []qm.QueryMod{qm.Where("age > 5")},
    []qm.QueryMod{qm.Where("age < 2")},
  ),
)

Would Children then get both the age < 3 and age > 5, or some other behavior?

[aarondl]

Ah, that's a good question. Since it's cached the second query wouldn't even be run. I'll discuss it with @nullbio some more. I'll try not to let it go so long this time. I'm in sqlboiler mode atm, few issues have piled up trying to take care of a bunch tonight.

[aarondl]

I think that the way to resolve this is to return an error on a query like this if the mod list is not null in the second set (cached result exists + query mod list exists). Although it is sort of counter-intuitive it will keep people safe from making mistakes. So far that's the only thing I've got in my head. Will probably implement like this over the holidays. Might also try to think of alternatives.

[gsamokovarov]

I just hit this in my application and had to filter the eagerly loaded relations closer to the request handlers side. Would love to be able to solve it close to the datastore.

[aarondl]

Yeah, it's a big shortcoming of that API and I intend to resolve it.

[gsamokovarov]

Thanks!

[aarondl]

Implemented in v3: a12d3af