Query with array problems

Vote:
 

Hi,

I have a forum where I want to implement a search using StarCommunity.Modules.Forum.Queries. When I want to search the topic.Text and topic.Subject for one word it works great, but when I have an array of words i cant get it to work without making more than one GetQueryResult, witch erases the possibility of using paging.

Is there any solution for this?

#21332
Jun 26, 2008 14:34
Vote:
 

Hi,

You can use the InCriterion of StringCriterion to filter on multiple values, like an SQL IN statement.

Like so:

TopicQuery tq = new TopicQuery();
tq.Subject = new StringCriterion();
tq.Subject.Includes = new StringInCriterion();
tq.Subject.Includes.Values.Add("string1");
tq.Subject.Includes.Values.Add("string2");

MessageCollection result = ForumHandler.GetQueryResult(tq);
#21337
Jun 26, 2008 16:09
Vote:
 

 I cant get it to work as I want with the souloution above, what I realy want to do is making a query that searches "%word1%" OR "%word2%" OR "%word3%" in both subject and text.

Right now i've got the folowing code:

string[] strSearchWords = Request.QueryString["search"].Trim().Split(' '); 
CriteriaGroup criteriaGroup = new CriteriaGroup(); 
MessageCollection topicCollection = new MessageCollection(); 
TopicQuery topicQuery = new TopicQuery(); 
topicQuery.Text = new StringCriterion(); 
topicQuery.Subject = new StringCriterion(); 
topicQuery.CreateDate = new DateTimeCriterion();  
//Set values to filter on 
topicQuery.Text.WildCardType = WildCardType.Both; 
topicQuery.Subject.WildCardType = WildCardType.Both; 
topicQuery.Subject.Includes = new StringInCriterion(); 
topicQuery.Text.Includes = new StringInCriterion(); 
topicQuery.Subject.Includes.Values.AddRange(strSearchWords); 
topicQuery.Text.Includes.Values.AddRange(strSearchWords);
 //Group Text and subject 
criteriaGroup.AddCriterion(topicQuery.Text); 
criteriaGroup.AddCriterion(LogicalOperator.Or, topicQuery.Subject); 
topicQuery.AddCriteriaGroup(criteriaGroup);
//Order by create date 
topicQuery.OrderBy.Add(new CriterionSortOrder(topicQuery.CreateDate, SortingDirection.Descending)); 
//Get the fitered topic collection 
topicCollection = ForumHandler.GetQueryResult(topicQuery);

If I have a topic with the text: Mycket bra forum, I can't get a match if i search with the words Mycket forum, they need to be in the right order to match, witch i want to avoid.

#21348
Edited, Jun 27, 2008 10:15
Vote:
 
Is there no soloution for this?
#21534
Jul 01, 2008 9:42
Vote:
 

Hi Niklas,

You are correct that wildcards are not possible inside a StringInCriterion, but it is possible to subclass it and override its behavior. In this example I have overriden the default IN (...) statement with a wildcard OR statement. I have also added the possibility to choose which kind of wildcard to use, so there's a little extra code for that also.

// The query execution...
TopicQuery tq = new TopicQuery();
tq.Subject = new StringCriterion();
tq.Subject.Includes = new CustomWildCardStringInCriterion();

tq.Subject.Includes.Values.Add("string1");
tq.Subject.Includes.Values.Add("string2");

// The subclassed InCriterion...
public class CustomWildCardStringInCriterion : StringInCriterion
{
    public CustomWildCardStringInCriterion()
    {
        this.WildCardType = WildCardType.Both;
    }

    public override string GetQuery(string propertyName)
    {
        if (Values != null && Values.Count > 0)
        {
            string parentPropertyName = ((CriterionBase)this.ParentQuery).AssignedPropertyPath;
            StringBuilder queryBuilder = new StringBuilder();
            queryBuilder.Append("(");
            foreach (string val in base.Values)
            {
                if (queryBuilder.Length > 1)
                    queryBuilder.AppendFormat(" {0} ", QueryBase.OR_OPERATOR);
                queryBuilder.AppendFormat("{0} LIKE {1}", parentPropertyName, this.FormatValue(val));
            }
            queryBuilder.Append(")");

            if (NullValueAction == NullValueAction.Include)
                queryBuilder.AppendFormat(" {0} {1} IS NULL", QueryBase.OR_OPERATOR, parentPropertyName);

            return queryBuilder.ToString();
        }
        else
            return string.Empty;
    }

    protected override string FormatValue(string val)
    {
        return String.Format("'{0}'", GetWildCardValue(val, this.WildCardType).Replace("'", "''"));
    }

    protected string GetWildCardValue(string val, WildCardType wildCardType)
    {
        if (wildCardType == WildCardType.None)
            return val;
        else
        {
            StringBuilder sb = new StringBuilder();

            if (wildCardType == WildCardType.Leading || wildCardType == WildCardType.Both)
                sb.Append(QueryBase.PERCENTAGE_OPERATOR);

            sb.Append(val);

            if (wildCardType == WildCardType.Trailing || wildCardType == WildCardType.Both)
                sb.Append(QueryBase.PERCENTAGE_OPERATOR);

            return sb.ToString();
        }
    }

    public virtual WildCardType WildCardType
    {
        get
        {
            return base.GetParameterValue<WildCardType>("WildCardType");
        }
        set
        {
            base.SetParameterValue<WildCardType>("WildCardType", value);
        }
    }
}

#21565
Edited, Jul 01, 2008 16:56
Vote:
 

Thanks a bunch Mattias, this seems to work. And as a bonus we got documentation on how to make our own critierions. Happy day!

Cool

#21590
Jul 02, 2008 9:32
This thread is locked and should be used for reference only. Please use the Legacy add-ons forum to open new discussions.