Create simplified doSelectJoin*() method(s)

[hans]

Currently there are many doSelectJoin*() methods being generated for all the possible related tables. This is resulting in very large classes containing many methods that are very similar. This needs to be fixed.

We can certainly create the needed Query objects (with joins) at runtime. The main problem is simply how to express this. There's also an advantage to having generated methods that know how tables are related and can construct the query accordingly.

Another option would be to discard these JOIN methods altogether, relying instead on speedy database connections and/or some sort of caching mechanism. I don't think there's a way to have Propel "predict" which tables should be JOINed in and do it transparently in the background. Joining all tables all the time would penalize typical non-join operations -- as the benefit of having all related objects on hand would almost never be fully utilized.

[apinstein]

Just wanted to post some thoughts and use cases for this one...

I ran into this situation tonight. I have a list view that pulls in data from the main table plus 2 relationships. Thus if this list view has 20 records, it has 1+2n (41) queries. I tried using doSelectJoinAll() but this caused -0- records to be selected since the appropriate semantic needed for my query is with a LEFT JOIN on some of the tables.

For now I have just switched to doSelectJoinOne() which cuts the number of queries down to 1+n, but a generalized doSelectJoin(array(Relationship1, Relationship2)) would be preferable. I may build a custom doSelectJoinAandB() but of course that's just for me.

Definitely we need a way to specify which *tables* to join, and which *method* to use for the join.

A few thoughts:

• Is the join method built-in to the relationship? That is, given some metadata about the relationship (one-to-zero-or-one, one-to-zero-or-more), do we always know the proper join method? Or are there cases where you want to LEFT JOIN sometimes, but CROSS JOIN others?
• How does propel currently store the "relationship" data? This is important in determining how to specify which tables to join... is there a constant or something generated for each relationship?
• Is the most flexible way to just specify a list of joins via LEFT TABLE, RIGHT TABLE, JOIN METHOD?

[andy]

Just realised this ticket exists(!) and an implementation is provided by #749.

The implementation in #749 provides a single doSelect()-style function for each peer that can take any Criteria and extracts the necessary metadata to do the joins and hydration from the maps. This approach requires that all joins are for "known" relationships defined in the schema.