BodyPart and a raw SQL

Topics: Troubleshooting
Jan 6, 2014 at 11:34 PM
Edited Jan 6, 2014 at 11:36 PM
Hi,

I need to create a blog posts feed from multiples blogs, filtered by terms. I try to use the ContentManager Query and HQLQuery but BlogPosts. But that approach did not worked, for example BlogPosts are built from many Parts (Title, Body, AutoroutePart) and each of this parts is a Lazy-loaded which is a problem to me (too many queries to the db).

So a have implemented a Raw SQL, it runs just fine for me but it has a problem.
SELECT DISTINCT TOP {0}
                        contentItemRecord.Id as Id, 
                        commonPart.Container_id as BlogId,
                        titlePart.Title as Title, 
                        cast(bodyPart.Text as varchar(max)) as Body,
                        autoRoutePart.DisplayAlias as Link, 
                        commonPart.PublishedUtc as PublishedUtc, 
                        commonPart.CreatedUtc as CreatedUtc, 
                        decimalFieldIndex.Value as DisplayOrder
                    
                    FROM Orchard_Framework_ContentItemVersionRecord contentItemVersion 
                    
                    INNER JOIN Orchard_Framework_ContentItemRecord contentItemRecord ON contentItemVersion.ContentItemRecord_id=contentItemRecord.Id 
                    INNER JOIN Orchard_Framework_ContentTypeRecord contentTypeRecord ON contentItemRecord.ContentType_id=contentTypeRecord.Id 
                    INNER JOIN Common_CommonPartRecord commonPart ON contentItemRecord.Id=commonPart.Id 
                    INNER JOIN Title_TitlePartRecord titlePart ON contentItemRecord.Id=titlePart.ContentItemRecord_id
                    INNER JOIN Common_BodyPartRecord bodyPart ON contentItemRecord.Id=bodyPart.ContentItemRecord_id
                    INNER JOIN Contrib_Taxonomies_TermsPartRecord termsPart ON contentItemRecord.Id=termsPart.Id 
                    INNER JOIN Contrib_Taxonomies_TermContentItem termContentItem ON termsPart.Id=termContentItem.TermsPartRecord_Id 
                    INNER JOIN Orchard_Projections_FieldIndexPartRecord fieldIndex ON contentItemRecord.Id=fieldIndex.Id 
                    INNER JOIN Orchard_Projections_DecimalFieldIndexRecord decimalFieldIndex ON fieldIndex.Id=decimalFieldIndex.FieldIndexPartRecord_Id 
                    INNER JOIN Orchard_Autoroute_AutoroutePartRecord autoRoutePart ON contentItemVersion.Id=autoRoutePart.Id 

                    WHERE (contentTypeRecord.Name IN ('BlogPost')) AND (commonPart.Container_id IN ({1})) 
                    AND (termContentItem.TermRecord_id IN ({2}))
                    AND contentItemVersion.Published=1 AND contentItemVersion.Latest=1
Some BlogPosts are duplicated because there are multiples BodyParts for the same ContentItem, and those parts have differeces in the BodyText. I though that using contentItemVersion.Published=1 AND contentItemVersion.Latest=1 I would avoid that. But every version of the BodyPart is published and is the Latets, maybe I'm missing something.

Any thoughts? Also if someone has any idea on how to write a better one query, that would be great.

I'm using Orchard 1.5.1
Thanks!
Coordinator
Jan 7, 2014 at 2:53 AM
You should never use raw SQL, as this is not going to be portable across database engines. Both ContentManager and HQL give you the ability to eagerly join.
Jan 7, 2014 at 12:12 PM
Hi Bertand,

Thanks for your response. I tried to use HQL to eagerly load the BlogPosts content but without success. I had this query :
_orchardServices.ContentManager.HqlQuery()
    .ForType("BlogPost")
    .ForVersion(VersionOptions.Published)
    .Join(alias => alias.ContentPartRecord<CommonPartRecord>())
    .Where(alias => alias.ContentPartRecord<CommonPartRecord>(),
               pr => pr.In("Container", blogIds))
    .Join(alias => alias.ContentPartRecord<TermsPartRecord>())
    .Where(alias => alias.ContentPartRecord<TermsPartRecord>().Property("Terms", "terms"),
              pr => pr.In("TermRecord", termsIds))
    .Join(alias => alias.ContentPartRecord<AutoroutePartRecord>())
    //Pinned
    .OrderBy(alias => alias.ContentPartRecord<FieldIndexPartRecord>().Property("DecimalFieldIndexRecords",
               propertyName.ToSafeName()), pr => pr.Desc("Value"))
    .OrderBy(alias => alias.ContentPartRecord<CommonPartRecord>(), pr => pr.Desc("CreatedUtc"))
    .List();
With this approach the Blog Posts where lazy loaded. We do not plan do change the db engine, so this is not a problem (now :p).

Thanks
Coordinator
Jan 8, 2014 at 8:12 AM
That query would also be easy to build with content manager.
Jan 8, 2014 at 2:04 PM
Edited Jan 8, 2014 at 2:05 PM
Hi,

I though that it was not possible to use content manger queries over ContentPart fields. I need to order the blog posts by DisplayOrder, on the HQL query I use FieldIndexPartRecord - DecimalFieldindexRecords. Is this right?

Where can I see eagerly join queries examples?.

Thanks
Coordinator
Jan 9, 2014 at 2:14 AM
I didn't notice the field, sorry. You're right. For examples on how to make Hql queries eager, you can look at the code for the new eager fetch query filter.
Jan 9, 2014 at 8:22 PM
Hi,

How about Lucene?. I know that is possible to index content on a custom Lucene index. Lucene should run pretty fast, the issue would be to have everything properly indexed.

I'll post later.