Joining Orchard part records in HQL

In yesterday’s post, I showed the basics of HQL querying in Orchard. It is by far the most flexible way to  query Orchard’s database, but one thing I didn’t show is how this works in relation to Orchard’s content type system. Querying over records is nice, but if those records are part records, you need to be really careful and check that they correspond to a real content item, that his content hasn’t been archived, and that its publication state is what you need it to be. In order to do that, you’ll have to join with ContentItemRecord and ContentItemVersionRecord. But how do you express joins in HQL in a way that works with Orchard records?

I would recommend you always start from the content item version record:

FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion

Then, you can join that to the content item record:

JOIN ItemVersion.ContentItemRecord Item

It’s easy to understand what’s going on here, because the ContentItemVersionRecord does have a ContentItemRecord property, that is of type ContentItemRecord. The mapping convention is easy and explicit here.

Joining with other part records, such as a UserPartRecord, is a little more puzzling, but works almost magically:

JOIN Item.UserPartRecord User

There is no UserPartRecord property on ContentItemRecord, so how does this work? Well, remember that Orchard creates mappings automatically from a bunch of conventions. It knows in particular that content items are aggregated from parts, and that when those parts are associated with a part record, joins should be made on the Id column of the part record and of the item record.

Finally, you’ll want to add some constraints, if you want to restrict what versions you want to get. For example, if you only want published versions, you’d add:

WHERE ItemVersion.Published = true

Wrapping it all together, here is a query that will get you all the ids for users that have no role:

SELECT DISTINCT User.Id
FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion
JOIN ItemVersion.ContentItemRecord Item
JOIN Item.UserPartRecord User
WHERE ItemVersion.Published = true
AND User.Id NOT IN (SELECT Role.UserId FROM Orchard.Roles.Models.UserRolesPartRecord Role)

Can you feel this series building itself? Next time, we’ll see how to get actual results out of those queries.