search

OrchardCMS / OrchardCore

Orchard Core is an open-source modular and multi-tenant application framework built with ASP.NET Core, and a content management system (CMS) built on top of that framework.
https://orchardcore.net
BSD 3-Clause "New" or "Revised" License
7.35k stars 2.37k forks source link

[feature] Auto transform Query Expression to SQL JSON Query with Document table #6096

Open RockNHawk opened 4 years ago

RockNHawk commented 4 years ago

Hi,

I know Orchard Core user YesSql as underlying, and Object store as JSON string in Document table, and provide an Index Provider API to expose object's Property or custom Data to another table to provide SQL field Query.

It's a cool idea,I have used it for a while, but I think it is a bit tedious. Can it be simplified? Can the Query API directly convert the query on Object to the native SQL JSON Document query? This is technically feasible, and MySQL, SQL Server, and SQLite all provide such support.

If this is possible, then for application scenarios where the amount of data is not large, you will no longer need to define the Index Provider.

Skrypt commented 4 years ago

Considering the fact that each RDBMS have their own implementation of the JSON function it would need to be something added in the Dialect of YesSQL. Here using IndexingProviders will always be the most performant solution and is part of the map/reduce design concept. Nothing prevents us looking at such option eventually but it's really going against the base design.

RockNHawk commented 4 years ago

@Skrypt Thanks for response, yes, IndexingProviders is the most performant solution, and have map/reduce feature.

Just sometimes performance is not the most important thing.

In Orchard module development, it is a bit tedious to define IndexProvider for every Entity that needs to be queried.

For small develop team,development quickly and use it, when the data becomes large, then switch to IndexProvider, development efficiency can be higher.

For module developer, some entity may never have amount of data, but have many query.

Maybe YesSQL can provide an extension API then we can self extend the Query parser for SQL JSON Document query, then YesSQL can merge them ?