Maybe A Simple Query or Not?

[smemis]

Hi,
I have a content type it's name is Article and it's content fields are title and subtitle.

How can i query (Lucene or Sql or Graphql) Article content type "... where title='ABC' order by subtitle desc"

I want to execute a simple query like this;
select * from Article where title="ABC" order by subtitle desc

Thanks.

[smemis]

I found a way like this:

SELECT json_extract(Content,'$.Article.Title.Text') as Title, json_extract(Content,'$.Article.SubTitle.Text') as SubTitle
FROM ContentItemIndex C
JOIN Document D ON D.Id = C.DocumentId
where ContentType = 'Article' and Latest=1 order by SubTitle desc

but is it correct to use json_extract for each field in the select?

[Skrypt]

Using a Lucene Query is more appropriate. These JSON functions are not supported by SQL Queries as far as I know. And they are different from one database provider to another.

[smemis]

Could you please show me how to do the query in this example with a simple Lucene query? I searched a lot, but I could not find any example or document.
Thanks in advance for your help.

[Skrypt]

There is an example in the Blog Recipe of Lucene Queries and also you need to refer yourself to the ElasticSeach Query documentation for examples. We also have examples in the Orchard Core documentation to help you.

{
  "query": {
    "bool": {
      "must" : {
          "term" : { "Content.ContentItem.ContentType" : "Article" }
      },
      "filter": [
        { "term": { "Content.ContentItem.Published" : "true" }},
        { "wildcard": { "Content.ContentItem.DisplayText" : "{{ liquid_var }}" }}
      ]
    }
  },
  "sort": [
    { "Content.ContentItem.DisplayText": { "order": "desc" } }
  ]
}

[smemis]

Thanks @Skrypt
if i sort DisplayText works, but if i sort on SubTitle Content Field it does not work :(
...
"sort": [
{ "Content.ContentItem.Content.Article.SubTitle.Text": { "order": "desc" } }
]
...
And i want to get a result set only Title and SubTiitle fields. How can i do that?

[Skrypt]

Make sure you have checked the Lucene index settings on your Field and make it "analyzed". Then make sure you are using the proper index column name.

[smemis]

Thank you so much @Skrypt
Final Lucene query sort section is;

"sort": {
"Article.SubTitle": { "order": "desc" }
}