I'd like to select all articles where title, subtitle, _header _or _body _ contains "foo" (adapter mode) without using full-text search. I can do

IPredicateExpression pe;
...
string pattern = "%foo%";
pe.AddWithOr(HelperClasses.ArticleFields.Title % pattern);
pe.AddWithOr(HelperClasses.ArticleFields.SubTitle % pattern);
pe.AddWithOr(HelperClasses.ArticleFields.Header % pattern);
pe.AddWithOr(HelperClasses.ArticleFields.Body % pattern);

which should produce

SELECT * FROM articles
WHERE title LIKE ?p1 OR subtitle LIKE ?p2 OR header LIKE ?p3 OR body LIKE ?p4

with all four parameters being equal to "%foo%" (or is it clever enough to use one unique parameter?).

I'm working on a project that uses direct SQL code and does

SELECT * FROM articles
WHERE str_ai_ci(title + ' ' + subtitle + ' ' + header + ' ' + body) LIKE str_ai_ci(?p1)

where str_ai_ci() is a custom database function which for a given string returns its ANSI, case-insensitive, no-accents version.

Is there a way to produce this w/LLBLGen?

Please check the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9144

Walaa wrote:

Please check the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9144

Thanks, I missed that one. So, I tried

IEntityField2 fn = ArticleFields.Id.SetExpression(ArticleFields.Title + " " + ArticleFields.SubTitle);

foreach (string pattern in query.Pattern.Split(' '))
{
    FieldLikePredicate like = new FieldLikePredicate(fn, null, "%" + pattern + "%");
    rpb.PredicateExpression.Add(like);
}

Which does produce

WHERE article.title + ' ' + article.subtitle LIKE '%foo%'

Which would be fine on MSSQL, however on MySql this is not how you concatenate strings (neither on Oracle, see the thread mentioned above). Shouldn't LLBLGen know how to concatenate strings? Anyway. I switched to

IRelationPredicateBucket rpb;

...

// this is mysql-specific
IEntityField2 fn = ArticleFields.Id.SetExpression(
    new DbFunctionCall("CONCAT_WS(' ', {0}, {1})", new object[] { ArticleFields.Title, ArticleFields.SubTitle })
);

foreach (string pattern in query.Pattern.Split(' '))
{
    FieldLikePredicate like = new FieldLikePredicate(fn, null, "%" + pattern + "%");
    rpb.PredicateExpression.Add(like);
}

which does produce

WHERE CONCAT_WS(' ', article.title, article.subtitle) LIKE '%foo%'

which is fine. Now the tricky part is trying to produce

WHERE STR_AI_CI(CONCAT_WS(' ', article.title, article.subtitle)) LIKE STR_AI_CI('%foo%')

because FieldLikePredicate wants a string, not an expression, as a pattern?

I suppose I could have a .Net implementation of STR_AI_CI and process the pattern on the .Net side... but I can think of some cases where it will not do. Such as, what if the pattern itself comes from another database field?

I suppose I could create a database function that would look like (pseudo-code)

function myfunc(a, b) { if str_ai_cs(a) like str_ai_cs(b) return 1 else return 0 }

and do

IRelationPredicateBucket rpb;

...

foreach (string pattern in query.Pattern.Split(' '))
{
    rpb.PredicateExpression.Add(
        ArticleFields.Id.SetExpression(
            new DbFunctionCall("MYFUNC(CONCAT_WS(' ', {0}, {1})",
                new object[] { ArticleFields.Title, ArticleFields.SubTitle }
            )
        ) == 1
    );
}

because then, pretty much everything is possible...

What do you think?

(edit: http://www.llblgen.com/TinyForum//Messages.aspx?ThreadID=10651 suggests using charindex/instr/... function instead of like when comparing two database fields)

What you run into is indeed a problem, MySql is the only db which uses a function to concat strings. You can always go for using a specific predicate class, based on the sourcecode of one of the predicate classes provided, to make coding the predicates less problematic.