HQL over multiple content parts

Topics: Customizing Orchard, Writing modules
Jul 31, 2012 at 3:20 PM

I'm working on a piece of logic that will enhance the taxonomy module for us with support for synonyms. For this I've added the Synonyms property to the TermPartRecord/TermPart combo.

In the UI I have added a new search box, so you have autocomplete for entering terms instead of just the tree. To support this behavior, I need a query that finds termparts in the configured taxonomy which have a title or synonym value that contains the term I've typed.

Sounds simple enough, but apparantly impossible for me to get working. I can't find the terms, because I cannot create a query on the contentmanager using the Query() method because I have to query over two content parts. So instead of going down that path I choose for the HqlQuery() method.

var query = _contentManager.HqlQuery().ForType(taxonomy.TermTypeName)
    .Join(alias => alias.ContentPartRecord<TitlePartRecord>())
    .Join(alias => alias.ContentPartRecord<TermPartRecord>())
    .Where(
        alias => alias.ContentItem(), 
        predicate => predicate.Or(left => left.Eq("Title",criteria),
            right => right.Eq("Synonyms",criteria)));

However, the above query results in an invalid query, because it cannot find the Title property or the Synonyms property.

How can I get the above query to work properly?

Coordinator
Jul 31, 2012 at 5:38 PM

The Where has to immediately follow the join that it uses from what I understand. Not sure why this could not be done with Query<TermPArtRecord>() with a join on TitlePartRecord.

Coordinator
Jul 31, 2012 at 5:53 PM

Do you really need Hql query here BTW ?

Jul 31, 2012 at 6:26 PM

I want the terms that match either on title or on synonyms. Are you guys positive that this is possible with just regular Content Query with a join and directly followed by a where clause?

Coordinator
Aug 2, 2012 at 4:47 PM

Not having tried it myself, I can't be 100% sure but it looks like it should work. Sébastien?

Developer
Jan 19, 2013 at 1:06 PM

I'm facing the exact same situation. I have a search term that I want to use in a query with two different record tables: a custom DocumentPartRecord.OriginalFileName and TitlePartRecord.Title.

I found the .Or method on the IHqlExpressionFactory, but it looks like the API only supports generating an OR expression for columns within the same table. My current code looks like this:

 private IHqlQuery<DocumentPart> GetDocumentsQuery(string searchTerm = null) {
    var query = _contentManager.HqlQuery().ForPart<DocumentPart>().Join(alias => alias.ContentPartRecord<TitlePartRecord>());
           
    if (!string.IsNullOrWhiteSpace(searchTerm)) {
        query.Where(
            alias => alias.ContentPartRecord<DocumentPartRecord>(),
            factory => factory.Or(
                lhs => lhs.InsensitiveLike("OriginalFileName", searchTerm, HqlMatchMode.Anywhere),
                rhs => rhs.InsensitiveLike("Title", searchTerm, HqlMatchMode.Anywhere)));
    }

    query.OrderBy(
        alias => alias.ContentPartRecord<TitlePartRecord>(),
        factory => factory.Asc("Title"));

    return query;
}

It does not work, since:

could not resolve property: Title of: KVV.DocumentManagement.Models.DocumentPartRecord [select civ from Orchard.ContentManagement.Records.ContentItemVersionRecord as civ join civ.ContentItemRecord as ci join civ.TitlePartRecord as titlePartRecord join ci.DocumentPartRecord as documentPartRecord where (lower(documentPartRecord.OriginalFileName) like '%intervi%' or lower(documentPartRecord.Title) like '%intervi%' ) AND (civ.Published = True) order by titlePartRecord.Title]

Does anyone know if the API supports generating queries where we can do OR and AND predicates across joined tables?
The Projections module is able to do an OR by grace of the groups, and is implemented by aggregating the results per group. I could do the same, but since I want to do a COUNT query and an actual slice query, that would mean I have to duplicate some code. Not a big deal, but it's not as nice.

 

 

Mar 23, 2013 at 4:59 PM
Hi sfmskywalker

Exactly the same problem. Have you succeeded whit it?
Developer
Mar 27, 2013 at 11:48 PM
I didn't, sorry. I did hear whispers about the API being revisited at some point. :)
Apr 3, 2013 at 1:21 PM
Thank you very much for let me know!!