Queries: Order results by field value in content

[neolution-ch2]

I have added a date field to my content type and have created some content items based on this type. I want to create a query to get these content items and sort them based on the date field.

First I tried to create an SQL query, but since the date field value is not a database column, but a JSON property. So my query needs to use the JSON_QUERY keyword. I have tested the query by running it against my MSSQL database and it works great. But Orchard seems not to support it.

After that, I tried to create a Lucene query. But in the admin, it seems the content is not even indexed. At least it's not displayed with the other columns that seem to represent the indexed database columns. Also I cannot find where I can choose which columns the index should index. Only the name of the index can be set.

Are there any other possibilities to do that?

[Skrypt]

One solution is to map the data with a custom IndexProvider. Then you could do a query on a datetime sql server column. Though, this should be easier than requiring this as like you have experienced the SQLParser doesn't understand JSON_QUERY and thus fails to validate the SQL.

5 options :

• Use SQL Field indexation feature with a Query.
• Implement aggregation queries in Lucene to allow ordering/grouping by month/year.
• Modify the SQLParser class to allow JSON_QUERY. Though right now this class only allows for SQL Syntax from a specific version/standard intentionally. And JSON functions are different from one DB Provider to an other. Probably why it's not allowed in the Queries.
• Create a custom IndexProvider in a custom module with a _session.Query<ContentItem, MapIndexExtensionNameClass().
• Use _session directly to create a SQL transaction with custom SQL.

[sebastienros]

@Skrypt should I remind you that you created the fields index feature? Please update your answer accordingly ;)

[dodyg]

@neolution-ch use https://orchardcore.readthedocs.io/en/dev/docs/reference/modules/SQL/#datetimefieldindex

1. Enable Content Fields Indexing (SQL) and SQL Queries features.
2. Then simply do the query directly from DateTimeFieldIndex table e.g

select DocumentId 
from DateTimeFieldIndex 
where ContentType = "YourContentTypeName" 
and ContentField = "YourDateFieldName" 
order by DateTime

[neolution-ch2]

@dodyg Great, thank you! Had to upgrade from beta3 to rc1. But looks like all works so far. I also activated the content field indexing module. How/When will the tables be populated? They're empty for me atm...

[dodyg]

How/When will the tables be populated?

I don't have a clue

[deanmarcussen]

@neolution-ch there maybe an issue in RC1 where the migration for the index fields didn't get populated. Are you sure you have the tables?

You'll possibly see an error in the logs. see #4406

The solution is either upgrade to dev myget feed, or register the missing migration in your own startup.

The migration to register would be services.AddScoped<IDataMigration, OrchardCore.ContentFields.Indexing.SQL.Migrations>();

Then not sure if they will index immediately, or if you will have to republish your content items to create indexes.

[dodyg]

if you will have to republish your content items to create indexes.

We need a functionality for this. Right now one would have to do it tediously one by one.

[Skrypt]

No the module doesn't populate the tables data. That's something that needs to be done manually as it requires using the different JSON functions of each DB SQL implementation.