Cayenne - Search for combination of dependencies

Question

I'm creating an application that allows users to create a form, which can then be loaded and filled out by another user, and that user's submission can then be viewed.

A Form is filled with Fields. When a user fills the form, a Submission database object is created, and this submission has a 1-M relationship with FieldValue objects. A FieldValue object has a FK to a Field, and stores a String of the user's input. Using this design, to view a submission, I read through the FieldValues associated with the Submission, and load the associated Field object, and fill it with the user's input. Everything works well in this sense, but my problem is in searching for these submissions.

I'm working on a search page, where I dynamically creat search fields based on the Fields of the Form that's being searched on. For example firstName and lastName. Let's say that the user searches with firstName = j lastName = smith. Using these search fields, I want to search for all submissions that have a FieldValue where the FK matches to firstName and the text contains "j" AND has A DIFFERENT FieldValue where the FK matches to lastName and the text contains "smith"

I have been trying variations of the following code:

 Expression exp = ExpressionFactory.matchExp(Submission.FORM_PROPERTY, _formId);

 for (SearchField searchField : searchFields)
 {                    
     Expression fieldExp = ExpressionFactory.matchExp(Submission.FIELD_VALUE_PROPERTY +"." + FieldValue.FIELD_PROPERTY, searchField.getFieldId());
     fieldExp = fieldExp.andExp(ExpressionFactory.likeIgnoreCaseExp(Submission.FIELD_VALUE_PROPERTY +"." + FieldValue.TEXT_PROPERTY, "%" + searchField.getText() + "%" ));

     exp = exp.joinExp(Expression.AND, fieldExp);                     
 }
 SelectQuery query = new SelectQuery(Submission.class, exp);

What I'm trying to do is loop through each of the search fields, and add it to the list of FieldValues that must be in the Submission. The problem with this is that it keeps searching for ONE FieldValue that has all of those values, and so, obviously fails. I have never done a search that could be a 1-M within another class, so I assume that I'm missing something here. Any help would be greatly appreciated. I apologize for the small novel in trying to describe what's going on, but it's a bit out of the ordinary for me.

Answer 1

You will need to build an Expression that creates M joins. "Splits" and "aliases" control how joins are generated. Since you have more than one criteria for each join, splits won't work, so using explicit aliases is more appropriate. Just let SelectQuery know what each alias means.

import static org.apache.cayenne.exp.ExpressionFactory;

int len = searchFields.size();
String[] aliases = new String[len];
for (int i = 0; i < len; i++) {
     SearchField f = searchFields[i];
     aliases[i] = f.getFieldId();                  
     Expression e = matchAllExp(alias +"." + FieldValue.FIELD_PROPERTY, f.getFieldId());
     e = e.andExp(likeIgnoreCaseExp(alias +"." + FieldValue.TEXT_PROPERTY, "%" + f.getText() + "%" ));

     exp = exp.joinExp(Expression.AND, e);                     
 }

 SelectQuery query = new SelectQuery(Submission.class, exp);
 query.aliasPathSplits(Submission.FIELD_VALUE_PROPERTY, aliases);