generate doSelectJoinXXXList for one-to-many relationships

[Remke Rutgers <Re...@brightalley.nl>]

Hello developers,

Torque generates protected static Peer methods to select objects with their
foreign-key related objects.
I thought it would be nice to have the same procedure for foreign-key
relationships the other way around. (one-to-many relationships)

As an example take the following situation:
I have ExamQuestion objects, related by a FK to Exam objects.
I have ExamAnswer objects, related by a FK to ExamQuestion object.
So torque generates (in BaseExamQuestionPeer):
    protected static List doSelectJoinExam(Criteria c)
        throws TorqueException

I would like to have a:
	static List do SelectJoinExamAnswer(Criteria c)
That way I can select and construct a list of ExamQuestion objects and their
associated ExamAnswer List objects without repeated queries per ExamQuestion
object.

algorithm:
*addSelectColumns for nested object
*addJoin
*doSelect
*loop through results
*	if new object create new instance and add subobject to the list
*	else add subobject to the list of the previously created object

So I copied the generated code and started modifying it (in
ExamQuestionPeer):
___________begin modified code__________________
	/**
	 * selects a collection of ExamQuestion objects pre-filled with
their
	 * ExamAnswer objects.
	 */
	public static List doSelectJoinExamAnswer(Criteria c)
		throws TorqueException
	{

		// Set the correct dbName if it has not been overridden
		// c.getDbName will return the same object if not set to
		// another value so == check is okay and faster
		if (c.getDbName() == Torque.getDefaultDB())
		{
			c.setDbName(DATABASE_NAME);
		}

		ExamQuestionPeer.addSelectColumns(c);
		int offset = numColumns + 1;
		ExamAnswerPeer.addSelectColumns(c);
		
		// the original criteria must be remembered to set
lastExamAnswersCriteria
		Criteria copyC = (Criteria) c.clone();
		
		c.addJoin(ExamQuestionPeer.ID, ExamAnswerPeer.QUESTION_ID);

		// note: the following selects more records than
ExamQuestion objects 
		// must be created
		List rows = BasePeer.doSelect(c); 
		
		List results = new ArrayList();

		// loop through the resultset
		for (int i = 0; i < rows.size(); i++)
		{
			Record row = (Record) rows.get(i);

			Class omClass = ExamQuestionPeer.getOMClass();

			// create a temporary ExamQuestion object
			ExamQuestion obj1 =
				(ExamQuestion)
ExamQuestionPeer.row2Object(row, 1, omClass);

			// create a temporary ExamAnswer object
			omClass = ExamAnswerPeer.getOMClass();
			ExamAnswer obj2 =
				(ExamAnswer) ExamAnswerPeer.row2Object(
					row,
					offset,
					omClass);

			boolean newObject = true;
			
			// loop through ExamQuestion objects added to the
results so far
			for (int j = 0; j < results.size(); j++)
			{
				ExamQuestion temp_obj1 = (ExamQuestion)
results.get(j);
				if
(temp_obj1.getPrimaryKey().equals(obj1.getPrimaryKey()))
				{
					// we found an ExamQuestion that we
already added to the results
					// so simply add the ExamAnswer
object to the ExamQuestion object
					newObject = false;
					temp_obj1.addExamAnswer(obj2);
					obj2.setExamQuestion(temp_obj1);
					break;
				}
			}
			if (newObject)
			{
				obj1.initExamAnswers();
				obj1.addExamAnswer(obj2);
				obj2.setExamQuestion(obj1);
				// don't modify the original criteria, we
need it in the next iteration
				Criteria questionCriteria = (Criteria)
copyC.clone();

	
questionCriteria.add(ExamAnswerPeer.QUESTION_ID, obj1.getId());
				
				// nasty: I want to do the following, but
lastExamAnswersCriteria is private
				//obj1.lastExamAnswersCriteria =
questionCriteria;
				results.add(obj1);
			}
		}
		return results;
	}
_____________end modified code_______________________

Note: I did NOT actually compile and test this code, because I realized that
it would never work, because I cannot access the lastExamAnswersCriteria
from ExamQuestionPeer, since it is private. (see the 'nasty' remark in the
code.
Thus the getExamAnswers(...) method in BaseExamQuestion will never return
the list from cache, because the lastExamAnswersCriteria is not matched.

You may provide me with comments on my code, but that is not my real
question.
The real question is:
- Does the approach I take sound useful/feasible?
- Does anyone see pitfalls?
- Would it be worth the trouble to provide an extension to the current
templates, so provide a public void setLastXXXCriteria(...) in BaseXXX and
provide protected static List doSelectJoinXXXList(...)? (aside from the fact
that I never wrote Velocity templates, so it would not be very trivial for
me)
.....

Any feedback is appreciated.

Remke Rutgers
Technisch Consultant Knowledge & Learning

Bright Alley Knowledge & Learning
Media Park - Sumatralaan 45
Postbus 10 - 1200 JB Hilversum
Web www.brightalley.com
Telefoon direct (035) 6775668
Fax (035) 6774355
E-mail remke.rutgers@brightalley.nl