Query including related content picker field content item

[fuzl-llc]

I'm trying to model a school where users can take courses. I created Course and UserCourse content types. Course has a name and description. UserCourse has a user (using UserPickerFeld), a course (using ContentPickerField), and a status (using an enum stored on UserCoursePart). So the JSON looks like this:

  "Status": 0,
  "User": {
    "UserIds": [
      "4v9kpp9vx1tr817g328t12am51"
    ]
  },
  "Course": {
    "ContentItemIds": [
      "413qw4pfxptk5ragzrrzvv8tcs"
    ]
  }
}

I can query for all Course content types using YesSql's ISession with something like this:

var contentItems = await _session.Query<ContentItem, ContentItemIndex>(index => index.ContentType == "Course").ListAsync();

...but is there any way related UserCourse content items for the current user can be included with the Course content items? I need it to return all Courses even if there is no UserCourse (e.g. a SQL left join on Course and UserCourse) so I can show a list of all courses with status indicators on those the current user is taking.

[fuzl-llc]

In case it might help someone, I have a solution that works... but I'd love to hear from one of the Orchard experts as I expect I'm not doing this the ideal way.

In any event, what I did is create a UserCoursePart index and index provider that makes UserId, Course ContentItemId, and Status easily available and queryable. I then created a class loosely based on SqlQuerySource that allows me to execute a raw SQL query. I can then do my left join on ContentItemIndex where ContentType is Course and the UserCoursePartIndex table joining on its CourseContentItemId with ContentItemIndex's ContentItemId. I also join in the Document table twice based on each index's DocumentId field and return the Document.Content field for both the Course and UserCourse. Finally when those two fields come back, I use the content serializer from the new YesSql.Configuration().ContentSerializer property to deserialize the Content fields into ContentItem objects, leaving the UserCourse ContentItem as null if there isn't one. This gets me both ContentItems I wanted with a single, fast SQL query.

I'm not sure if this is crazy or the right thing to do (or somewhere in between). I'm going to try it out for a while and see if I run into any issues. In the meantime, hopefully one of the experts will weigh in.

[jtkech]

Normally you don't need to use the serializer if content items (parts/fields) are correctly loaded by the content manager and then activated. We have extension helpers to still use _session.Query() but by also passing the IContentManager.

OrchardCore/src/OrchardCore/OrchardCore.ContentManagement.Abstractions/ContentQueryExtensions.cs

Lines 17 to 34 in 92daf3e

	public static async Task<ContentItem> FirstOrDefaultAsync(this IQuery<ContentItem> query, IContentManager contentManager)
	{
	var contentItem = await query.FirstOrDefaultAsync();
	if (contentItem == null)
	{
	return null;
	}
	return await contentManager.LoadAsync(contentItem);
	}
	/// <summary>
	/// Executes this <see cref="IQuery{ContentItem}"/> to return all matching <see cref="ContentItem"/>s
	/// but after having invoked the load events on them by using the <see cref="IContentManager"/>.
	/// </summary>
	public static async Task<IEnumerable<ContentItem>> ListAsync(this IQuery<ContentItem> query, IContentManager contentManager)
	=> await contentManager.LoadAsync(await query.ListAsync());

Then there are YesSql helpers to do more complex queries joining on more than one index table, we have e.g. IsIn<>() and IsNotIn<>() (not those used with simple collection) allowing to join to another index, need to retrieve an example.

Okay, this one

OrchardCore/src/OrchardCore/OrchardCore.OpenId.Core/YesSql/Stores/OpenIdTokenStore.cs

Lines 438 to 444 in 92daf3e

	var tokens = await _session.Query<TToken, OpenIdTokenIndex>(
	token => token.CreationDate < threshold.UtcDateTime &&
	((token.Status != Statuses.Inactive && token.Status != Statuses.Valid) ||
	token.AuthorizationId.IsNotIn<OpenIdAuthorizationIndex>(
	authorization => authorization.AuthorizationId,
	authorization => authorization.Status == Statuses.Valid) ||
	token.ExpirationDate < DateTime.UtcNow), collection: OpenIdCollection).Take(100).ListAsync();

[fuzl-llc]

Thanks for the reply. Those extension helpers are new to me but I'm not sure how it would help my case of wanting to write a single SQL query that returns more than one ContentItem per matching result/row. It seems like it can only create a single content item for each returned row (or the first as in the case you give above). I've also seen the YesSql multi-index query functionality but it too (I think) just expects there to be a single document to serialize per row.

Imagine a query that returns 100 Course content items and 5 of them have a related UserCourse content item (meaning the current user is taking just those 5 courses). I want a single query that returns all 100 courses and the 5 UserCourses. So, if the first Course has a related UserCourse content item, I'd get two content items deserialized, one for the Course and one for the related UserCourse. If the second Course does not have a related UserCourse I'd just get a single content item for the Course.

Examples I've found of similar desires all seem to require you to do multiple queries where each query automatically deserializes the lone document table "Content" field returned by the query.

[jtkech]

If perf is a concern you have to check, sometimes better to use 2 simple queries in place of a too complex one implying multiple joins. In that case you can do first a direct _session.QueryIndex<>( x => delegate) on a table index that for e.g. is populated with multiple content item Ids and other useful fields to make the relation and retrieve e.g. item Ids.

You can also cache relations, not full items but related Ids, the cache being populated from the index table on startup and invalidation (e.g. through an handler on saving). We have services and helpers for this but that's another story ;)

[jtkech]

Oops just saw your last comment, will see tomorrow

Yes in the extensions I mentioned there is FirstOrDefaultAsync() that returns only one item, but there is also ListAsync() just after.

[fuzl-llc]

Looks like we posted at the same time... so not sure you saw my last one. Perf is definitely a concern. One clarification to my previous post - I want the 100 Courses and the 5 UserCourses in one query and that query should have 100 total result records. E.g. it has two fields: "Course" and "UserCourse," and "UserCourse" is null 95 of the 100 times but all 100 have a non-null Course. I had a way to do this with multiple queries and I was stiching the objects together (setting a User.UserCourse property) but it seemed very hacky. I think in this case it is just three simple joins - I'm not a SQL expert but have always preferred some basic joins over multiple queries. Maybe I'm totally misunderstanding the way to implement related content items that have many to many relationship.

[jtkech]

Okay would need to look at it more in details.

Hmm, to return 2 types of items you may need to still use ContentItemIndex and 2 other IndexTable1 and IndexTable2, both having a DocumentId column as we do in the source code in many places, maybe something like this.

var query = _session.Query<ContentItem, ContentItemIndex>(i => (conditions on ContentItemIndex) &&
    (i.SomeId.IsIn<IndexTable1>(i => conditions on index1) ||
    i.SomeId.IsIn<IndexTable2>(i => conditions on index2));

[fuzl-llc]

This was my original thinking but, after doing a bunch of YesSql reading and poking around the source, it seemed like _session.Query<ContentItem, ContentItemIndex> is saying return (a single) content item per matching record, based on filtering on ContentItemIndex. You can add additional filtering indexes using .With<OtherIndex> and passing the other indexes, but I thought it still only returned the one ContentItem per result record... but I'm not 100% sure.

[jtkech]

Not done in my above example where I forgot the selector part of the IsIn<>() to do the join. But IsIn<>() also allows to add inside a predicate on index1, another predicate on index2 based on the value of a field of index1.

i.SomeId.IsIn<IndexTable1>(x =>(the SomeId selector), i => (conditions on index1) ||
   i.SomeIndex1Id.IsIn<IndexTable2>(x =>(the SomeIndex1Id selector), i => (conditions on index2))

Which .With<OtherIndex> doesn't allow.

Note: There are other Any() and All() helpers on wihich you can pass multiple predicates, see https://github.com/sebastienros/yessql/blob/0b8dd422407b5216882d2dc990d74ab29c4e56cd/src/YesSql.Abstractions/IQuery.cs#L34

[fuzl-llc]

Thanks for the link, it is definitely helpful in understanding this better, and it does seem to be getting to the join functionality I'm looking for, but I don't see how it returns content from both the main content item from ContentItemIndex and another content item from another index.

On the line above the one you reference in the link, line 33, it has a comment for the generic type parameter T: "The type to return. It can be and index or an entity." This again seems to confirm all of these methods are designed to return a single content item object. And in looking at the tokes query you reference:

var tokens = await _session.Query<TToken, OpenIdTokenIndex>(

...it seems to be returning just the single TToken type as a list of token objects (even though it used multiple indexes to get the desired tokens). Are there any examples that return two related content items deserialized from document table's Content field as a single record?

[jtkech]

Hmm yes but if you use .Query<ContentItem, SomeIndex>()
Which is the same as .Query<ContentItem>().With<SomeIndex>()

It will return content items of any types (content item types, not c# types) you are filtering.

var query = ...
var items = await query.ListAsync(_contentManager);
foreach (var item in items)
{
    // You can also check item.ContentType == "SomeType".
    var part = item.As<SomePart>();
    if (part != null)
    ....
}

In your filtering you may also need to check Published and maybe Latest that are part of ContentItemIndex, but they can be also part of your index tables and populated in your custom index providers.

Assuming that index1 is only related to a given item type1, and index2 to a type2, if we don't use ContentItemIndex but e.g. first index1 and in its delegate use the IsIn<>() joining to index2, you will only return items of type1.

That's why, if we only use such extensions as IsIn<>(), I think the query should be first based on ContentItemIndex that contains all content item types, and the in its delegate use such extensions.

Or only use index1 and index2 but by using the other extension as maybe .Any<>() which I think allows to pass multiple delegates applied on their related index tables, and then to return all items for which any delegate returns true.