Ticket

Filtering on a related object property

I'm trying to improve some bits of our application. I have a relationship between content that looks like this:

A Group has many Lists has many Events.

Group, List and Event are all content items with a corresponding part record for each type with links. Currently to find all events in a group I use HQL to find the ids then pull these using GetMany. This works fine, I'm just wondering if I can make a query like this work:

var videoQuery = _contentManager
    .Query(VersionOptions.Published)
    .Join<EventContainablePartRecord>()
    .Where(x => x.EventListPartRecord.EventGroupPartRecord.Id == 13)
    .List();

This query fails, it cant find EventGroupPartRecord property. Is there a way I can expand the linked property somehow? I've had a quick google but cant find any examples and I tried QueryHints but they don't seem to be what I'm looking for. It is interesting how the queries are translated because it is possible to reference an object's Id like this:

var lists = _contentManager
    .Query(VersionOptions.Published)
    .Join<EventListPartRecord>()
    .Where(x => x.EventGroupPartRecord.Id == group.Id)
    .List()

You can only access the Id property of EventGroupPartRecord, no other properties.

I had a look at taxonomies, they have queries that reference collections within an object. I can make queries like that too.

var test = _contentManager.Query<EventGroupPart, EventGroupPartRecord>()
                .Where(e => e.EventListParts.Any(b => b.Events.Any(t => t.Id == 12))).List();

This query works. Does nHibernate know to build subqueries for these collections? So I can traverse the relationship from the top entity but not from the bottom entity?

Re: Filtering on a related object property

With an HqlQuery() you can create a join based on a property. So, maybe a similar scenario with the following example that uses the DecimalFieldIndexRecords collection property of a FieldIndexPartRecord.

        var items1 = _contentManager.HqlQuery()
            .ForType("Page")
            .ForVersion(VersionOptions.Published)
            .Join(x => x.ContentPartRecord<FieldIndexPartRecord>()
            .Property("DecimalFieldIndexRecords", "fieldRecord"))
            .Where(x => x.Named("fieldRecord"), x => x.And(
                a => a.Eq("PropertyName", "Page.SortOrder."), b => b.Eq("Value", 10)))
            .List();

For more complex queries, you can also use NHibernate QueryOver() that allows lambda and linq expressions, joining and subqueries... Here an quick example inspired on the above query. Note: here i didn't need to use a subquery.

...
using Orchard.ContentManagement.Records;
using Orchard.Data;
using Orchard.Projections.Models;
...

        FieldIndexPartRecord fieldIndex = null;
        ContentItemRecord itemRecord = null;
        ContentTypeRecord typeRecord = null;
        DecimalFieldIndexRecord decimal1 = null;

        var items2 = session.QueryOver<FieldIndexPartRecord>(() => fieldIndex)
            .JoinAlias(() => fieldIndex.ContentItemRecord, () => itemRecord)
            .JoinAlias(() => itemRecord.ContentType, () => typeRecord)
            .Where(() => typeRecord.Name == "Page")
            .JoinQueryOver(x => x.DecimalFieldIndexRecords, () => decimal1)
            .Where(() => decimal1.PropertyName == "Page.SortOrder."
                && decimal1.Value == 10)
            .List();

Best

Wednesday, September 7, 2016 10:22:24 PM byjtkech
  • jtkech
  • Lv. 13 Rookie
  • Total EXP: 1028

Post a reply

You need to be signed in to post a reply.

Sign In