SQL search

[asemandeveloper]

Hello everyone
I have added some fields of different types (text, number, date and image) in BlogPost type, I want to select BlogPosts by SQL search and display them on LandigPage. How do I do this? please guide me.
thank you

[asemandeveloper]

Is it really not possible to write a SQL query in Orchard Core to search and display the fields of a content type and display the result on a page or landing page?

[hishamco]

You can endable SQL Queries feature, then you can write your SQL query, after that you could use IOrchardHelper.Query to display the result

[asemandeveloper]

Hi Hisham Abdullah
I have enabled SQL Search feature, My content type has these three fields: Name, LastName, Image. Please write the SQL query, guide me further.
Also tell me how to call SQL query on the page or landing page.
Thank you in advance.

[hishamco]

Try SELECT * FROM ContentItemIndex WHERE ContentType='YourContentTypeName'

[asemandeveloper]

SQL Query is:
SELECT * FROM [ContentItemIndex] WHERE [ContentType] = 'Information' ORDER BY [CreatedUtc] DESC LIMIT 6;

and result is without Name, LastName and image:

[
    {
        "Id": 399,
        "DocumentId": 112,
        "ContentItemId": "4j8km7zsy3tz40wp6er80a5hmp",
        "ContentItemVersionId": "4nrhhhvq7m6qv0zkdh5d4ny9c2",
        "Latest": 1,
        "Published": 1,
        "ContentType": "Information",
        "ModifiedUtc": "2023-03-06 15:30:24.9154006",
        "PublishedUtc": "2023-03-06 15:30:25.0064275",
        "CreatedUtc": "2023-03-06 15:30:24.9154006",
        "Owner": "4hr529zkhbvc94nmg4h53wk1n1",
        "Author": "akbar",
        "DisplayText": null
    }
]

But i want the Name, LastName and Image to be in the search result.

[hishamco]

You can get them by joining the Document table

[asemandeveloper]

Because the field values of a type are stored as records of a table "for example Text Field values stored in TextFieldIndex table", joining does not return the correct result.

Please write a query. For example

[hishamco]

You can extract the content details from Document.Content in your view using dynamic binding

[asemandeveloper]

How?
Give more guidance.

[hishamco]

Try this one SELECT * FROM [ContentItemIndex], [Document] WHERE [ContentItemIndex].[DocumentId] = [Document].[Id] AND [ContentType] = 'YourContentTypeName';

The content field should contains your content type fields

[asemandeveloper]

Thanks a lot
Now, how do I separate the Content values?

It is better to fully explain my purpose.

1. I have written a content type that has several fields
2. I want to display the last six records of the content type with all its fields on the landing page without coding and only with SQL Query.

[asemandeveloper]

Thank you for everything
With the following codes, the problem is solved.

Query RecentInformation is:

SELECT Content
FROM [ContentItemIndex], [Document] 
WHERE [ContentItemIndex].[DocumentId] = [Document].[Id] AND [ContentType] = 'Information';

Code in LandingPage is:

{% assign recentInformation = Queries.RecentInformation | query %}
{% for item in recentInformation %}
    {% assign jsonObject = item.Content | jsonparse %}
    {{ jsonObject.Information.Name.Text}} {{ jsonObject.Information.LastName.Text}}
{% endfor %}

[hishamco]

Then I realize that are you using some code at the end ;)

[asemandeveloper]

By coding, I mean C# code!

[hishamco]

I can do the same thing with Razor syntax which requires a little C# code, second time be explicit "Using Liquid" :)