Distinct items of a part

Topics: Writing modules
Nov 16, 2013 at 12:51 AM
Edited Nov 16, 2013 at 1:57 AM
I am looking for a good way to get the distinct items of a field on a part. Here is what I have come up with but assuming the performance here is pretty bad. Is there a way to handle this type of query in a better way.
return this.contentManager.HqlQuery()
                .ForPart<TestPart>()
                .List()
                .Select(c => c.Speaker)
                .Distinct(StringComparer.OrdinalIgnoreCase);
Any insights is truly appreciated,
Matt
Coordinator
Nov 16, 2013 at 2:23 AM
What is an "item of a field"? Seems like the huge performance problem you're creating here is that you are getting all content items with that part from the database, and then get the distinct speakers. You should let the database do the work instead, so get the "distinct" working in Hql rather than in memory.
Nov 16, 2013 at 2:56 AM
Really was meaning a property.

TestPart
-Speaker <== I want distinct speaker, easy in SQL not sure how to efficiently query via Orchard APIs
-Bunch of other Properties

I realize the code initially listed is bad (really bad), not sure exactly how to distinct via the db rather than in memory. This is being used for a WebAPI to get the list of possible speakers.

Thanks,
Matt
Coordinator
Nov 16, 2013 at 5:32 AM
What is Speaker? Is it a property of the TestPart part?
Nov 16, 2013 at 5:46 AM
That is correct, of type string.

Thanks,
Matt
Coordinator
Nov 16, 2013 at 5:57 AM
What DefaultHqlQuery is doing is create a hql string query from a more structured object model. That model doesn't support distinct at this time, but that doesn't mean you can't do what DefaultHqlQuery is doing, but with your own custom hql string.
Nov 16, 2013 at 7:03 AM
I think I am getting closer, but still not sure how to query on specific properties on part using custom hql query. I will need to learn a bit of NHibernate as I pretty new at that as well. Here is what I found so far looking at the class you mentioned.
var service = this.sessionLocater.Value.For(typeof(ContentItemVersionRecord));
            var hql = "select civ from Orchard.ContentManagement.Records.ContentItemVersionRecord as civ where (civ.Published = True)";
            var query = service
               .CreateQuery(hql)
               .SetCacheable(true)
               .SetResultTransformer(new DistinctRootEntityResultTransformer());
            var temp = query.List<ContentItemVersionRecord>().Select(x => this.contentManager.Get(x.Id, VersionOptions.VersionRecord(x.Id)));
Thanks for the help,
Matt
Coordinator
Nov 16, 2013 at 7:07 AM
Sure, good luck with the hql.
Nov 16, 2013 at 7:38 AM
Edited Nov 16, 2013 at 8:23 AM
So I thought I almost had it, but alas it just dies with no apparent exception or log entry, not really sure why. I can’t figure out how to get the object down to just the one property. Feel like I am so close :).
            var service = this.sessionLocater.Value.For(typeof(ContentItemVersionRecord));
            var hql = "select civ from Orchard.ContentManagement.Records.ContentItemVersionRecord as civ where (civ.Published = True)";
            var query = service
               .CreateQuery(hql)
               .SetCacheable(true)
               .SetResultTransformer(new DistinctRootEntityResultTransformer());
            var temp = query
                    .List<ContentItemVersionRecord>()
                    .Select(x => this.contentManager.Get(x.Id, VersionOptions.VersionRecord(x.Id)))
                    .AsPart<TestPart>()
                    .AsQueryable();

            var speakers = temp.Select(s => s.Speaker).Distinct(StringComparer.InvariantCultureIgnoreCase).ToList();  // Seems to Silently Fail here >Select seems to be the root cause
Thanks,
Matt
Nov 16, 2013 at 8:23 AM
Edited Nov 16, 2013 at 8:24 AM
I think I got it :)
var service = this.sessionLocater.Value.For(typeof(ContentItemVersionRecord));
            var hql = "select distinct civ.Speaker from TestData.Models.TestPartRecord as civ";
            var query = service
               .CreateQuery(hql)
               .SetCacheable(true)
               .SetResultTransformer(new DistinctRootEntityResultTransformer());

            return query.List<string>().ToList();
Thanks for all your help, this appears to be working and a lot more performing!
Matt
Nov 16, 2013 at 6:14 PM
Here is an even better answer as it selects only from the published records.
            var service = this.sessionLocater.Value.For(typeof(ContentItemVersionRecord));
            var hql = "select civ from Orchard.ContentManagement.Records.ContentItemVersionRecord as civ where (civ.Published = True)";
            var query = service
               .CreateQuery(hql)
               .SetCacheable(true)
               .SetResultTransformer(new DistinctRootEntityResultTransformer());
            
            return query.List<ContentItemVersionRecord>()
                    .Select(x => this.contentManager.Get(x.Id, VersionOptions.VersionRecord(x.Id)))
                    .AsPart<TestPart>()
                    .Select(x => x.Speaker)
                    .Distinct(StringComparer.InvariantCultureIgnoreCase)
                    .OrderBy(x => x);
Again thinks for the assist BertrandLeRoy,
Matt
Marked as answer by MatthewDubbs on 11/16/2013 at 10:14 AM