Hi.

My llblgen version is 2.0.0.0 Final, 6th December.

I have a method to fetch entities by filter. I am posting the whole thing at the end of my post, though you probably dont need the whole thing to answer my question.

The filter method accepts a free text string entered directly by the user. The search string "February 2008" should be narrower than just "February". The way sql FreeText works, this is not the case. The result set of the former search string will contain any entity where either "February" or "2008" is used. What is the best way to code a search, using llblgen, that becomes more narrow with more freetext words (like an ordinary google search).

Thx, Tore

public EntityCollection<ReportInstanceEntity> FetchReportInstancesByFilter(long reportTypeId, string reportNumber, bool? isValid, string submittedByUser, DateTime? submittedFromDate, DateTime? submittedToDate, string lastSavedByUser, DateTime? lastSavedFromDate, DateTime? lastSavedToDate, string startedByUser, DateTime? startedFromDate, DateTime? startedToDate, string freeText) {
           using (DataAccessAdapter adapter = new DataAccessAdapter()) {
                EntityCollection<ReportInstanceEntity> reportInstances = new EntityCollection<ReportInstanceEntity>();
                IRelationPredicateBucket filter = new RelationPredicateBucket();
                filter.Relations.Add(ReportInstanceEntity.Relations.WizardStepDataRowInstanceEntityUsingReportInstanceId);
                filter.Relations.Add(WizardStepDataRowInstanceEntity.Relations.WizardStepDataFieldValueEntityUsingWizardStepDataRowInstanceId);
                if (reportTypeId > 0) {
                    filter.PredicateExpression.Add(new FieldCompareValuePredicate(ReportInstanceFields.ReportTypeId, null, ComparisonOperator.Equal, reportTypeId));
                }
                if (reportNumber.Length > 0) {
                    filter.PredicateExpression.Add(new FieldLikePredicate(ReportInstanceFields.ReportNumber, null, "%" + reportNumber + "%"));
                }
                if (isValid != null) {
                    filter.PredicateExpression.Add(new FieldCompareValuePredicate(ReportInstanceFields.IsValid, null, ComparisonOperator.Equal, isValid));
                }

                if (!string.IsNullOrEmpty(submittedByUser)) {
                    filter.PredicateExpression.Add(new FieldCompareValuePredicate(ReportInstanceFields.SubmittedByUser, null, ComparisonOperator.Equal, submittedByUser));
                }
                if (submittedFromDate != null && submittedToDate != null) {
                    filter.PredicateExpression.Add(new FieldBetweenPredicate(ReportInstanceFields.SubmittedDate, null, submittedFromDate, submittedToDate.Value.AddDays(1)));
                }

                if (!string.IsNullOrEmpty(lastSavedByUser)) {
                    filter.PredicateExpression.Add(new FieldCompareValuePredicate(ReportInstanceFields.LastSavedByUser, null, ComparisonOperator.Equal, lastSavedByUser));
                }
                if (lastSavedFromDate != null && lastSavedToDate != null) {
                    filter.PredicateExpression.Add(new FieldBetweenPredicate(ReportInstanceFields.LastSavedDate, null, lastSavedFromDate, lastSavedToDate.Value.AddDays(1)));
                }

                if (!string.IsNullOrEmpty(startedByUser)) {
                    filter.PredicateExpression.Add(new FieldCompareValuePredicate(ReportInstanceFields.StartedByUser, null, ComparisonOperator.Equal, startedByUser));
                }
                if (startedFromDate != null && startedToDate != null) {
                    filter.PredicateExpression.Add(new FieldBetweenPredicate(ReportInstanceFields.StartedDate, null, startedFromDate, startedToDate.Value.AddDays(1)));
                }
                if (!string.IsNullOrEmpty(freeText)) {
                    //string[] freeTextSearchArray = freeText.Split(' ');
                    //foreach (string freeTextString in freeTextSearchArray) {
                        filter.PredicateExpression.Add(new FieldFullTextSearchPredicate(WizardStepDataFieldValueFields.Value, null, FullTextSearchOperator.Freetext, freeText));
                    //}
                }
                adapter.FetchEntityCollection(reportInstances, filter);
                return reportInstances;
            }           
        }

There is no better example than the Search of this Forum. This Forum souce code is available here: http://www.llblgen.com/HnD/

Hi.

Thank you very much for the tip. Excellent search, but it is not what I want. In terms of the forum search, I want to search by Thread, not message. E.g., if a thread T1 contains message M1 and M2, a search by "Criteria1 AND Criteria2" would return T1 even if M1 contains only Criteria1 and M2 only Criteria2.

How would I modify the code below to achieve this?:

searchTermFilter.Add(new FieldCompareSetPredicate(ThreadFields.ThreadID, MessageFields.ThreadID,
                                    SetOperator.In, new FieldFullTextSearchPredicate(MessageFields.MessageText, FullTextSearchOperator.Contains, searchTerms)));

hotchill wrote:

Hi.

Thank you very much for the tip. Excellent search, but it is not what I want. In terms of the forum search, I want to search by Thread, not message. E.g., if a thread T1 contains message M1 and M2, a search by "Criteria1 AND Criteria2" would return T1 even if M1 contains only Criteria1 and M2 only Criteria2.

How would I modify the code below to achieve this?:

searchTermFilter.Add(new FieldCompareSetPredicate(ThreadFields.ThreadID, MessageFields.ThreadID,
                                    SetOperator.In, new FieldFullTextSearchPredicate(MessageFields.MessageText, FullTextSearchOperator.Contains, searchTerms)));

Isn't that select * from thread where threadid in ( select threadid from message where criteria1 or criteria2)

?

the where .. in is a fieldcompareset predicate, the where criteria1 or criteria2 is just a predicate expression with 2 predicates.

You need an or, if the criteria test on the same field, and the field can have just 1 value.

Hi Otis

Your suggestion would return thread T1 if a single message in it satisfies Criteria1 or Criteria2. I want T1 with messages M1, M2 and M3 if:

• M1 satisfies Criteria1 and M2 satisfies Criteria2.
• Or M3 satisfies Criteria1 and Criteria2.

Intersections of ThreadId sub selects?

I think you might need something like the following query: (not tested)

(
  select * from thread where threadid in
  ( select threadid from message where criteria1)
) t1
INNER JOIN
(
  select * from thread where threadid in
  ( select threadid from message where criteria2)
) t2
Where t1.threadid = t2.threadid

... which isn't possible at the moment, however you could rewrite these criteria as:

where
(
    threadid in
    (
        select threadid from message
        where criteria1
    )
    and
    threadid in
    (
        select threadid from message
        where criteria2
    )
)
or
(
    threadid in
    (
        select threadid from message
        where criteria1 and criteria2
    )
)

Thank you very much guys. I got it working now:

if (!string.IsNullOrEmpty(freeText)) {
                   List<string> andTerms = new List<string>(), orTerms = new List<string>();
                    PrepareSearchTerms(freeText, andTerms, orTerms);                    
                    IPredicateExpression freeTextFilter = new PredicateExpression();
                    foreach (string term in andTerms) {
                        FieldCompareSetPredicate innerCompare = new FieldCompareSetPredicate(WizardStepDataRowInstanceFields.WizardStepDataRowInstanceId, null, WizardStepDataFieldValueFields.WizardStepDataRowInstanceId, null,
                            SetOperator.In, new FieldFullTextSearchPredicate(WizardStepDataFieldValueFields.Value, null, FullTextSearchOperator.Contains, term));
                        freeTextFilter.AddWithAnd(innerCompare);
                    }
                    foreach (string term in orTerms) {
                        FieldCompareSetPredicate innerCompare = new FieldCompareSetPredicate(WizardStepDataRowInstanceFields.WizardStepDataRowInstanceId, null, WizardStepDataFieldValueFields.WizardStepDataRowInstanceId, null,
                            SetOperator.In, new FieldFullTextSearchPredicate(WizardStepDataFieldValueFields.Value, null, FullTextSearchOperator.Contains, term));
                        freeTextFilter.AddWithOr(innerCompare);
                    }
                    FieldCompareSetPredicate outerCompare = new FieldCompareSetPredicate(ReportInstanceFields.ReportInstanceId, null, WizardStepDataRowInstanceFields.ReportInstanceId, null, SetOperator.In, freeTextFilter);
                    filter.PredicateExpression.Add(outerCompare);
}

uhum...last post was kinda wrong.

if (!string.IsNullOrEmpty(freeText)) {
                    List<KeyValuePair<string, string>> terms = PrepareSearchTerms(freeText);                    
                    IPredicateExpression freeTextFilter = new PredicateExpression();
                    foreach (KeyValuePair<string, string> term in terms) {
                        FieldCompareSetPredicate innerCompare = new FieldCompareSetPredicate(WizardStepDataRowInstanceFields.WizardStepDataRowInstanceId, null, WizardStepDataFieldValueFields.WizardStepDataRowInstanceId, null,
                            SetOperator.In, new FieldFullTextSearchPredicate(WizardStepDataFieldValueFields.Value, null, FullTextSearchOperator.Contains, term.Value));
                        switch(term.Key) {
                            case "AND":
                                freeTextFilter.AddWithAnd(innerCompare);
                                break;
                            case "OR":
                                freeTextFilter.AddWithOr(innerCompare);
                                break;
                        }
                    }
                    FieldCompareSetPredicate outerCompare = new FieldCompareSetPredicate(ReportInstanceFields.ReportInstanceId, null, WizardStepDataRowInstanceFields.ReportInstanceId, null, SetOperator.In, freeTextFilter);
                    filter.PredicateExpression.Add(outerCompare);
}