Expose SQL Queries in GraphQL too

[Piedone]

Is your feature request related to a problem? Please describe.

Related: #15959. Lucene and Elasticsearch Queries are accessible from GraphQL, see here. This makes it possible to expose arbitrary content lists in GraphQL. However, the same feature is not available for SQL Queries.

Describe the solution you'd like

Have the same kind of GraphQL integration for SQL Queries that we have for the other two.

Describe alternatives you've considered

Alternatively, we could somehow expose field querying with Content Fields Indexing (SQL) in GraphQL, also see #15959 (comment) and #7047, #12781 about this.

[hyzx86]

I've introduced this Orm framework into my framework, mainly for implementing full table indexing, but this feature should be available for reference

DynamicFilterInfo dyfilter = JsonConvert.DeserializeObject<DynamicFilterInfo>(@"
{
  ""Logic"": ""And"",
  ""Filters"":
  [
    { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 1 },
    {
      ""Logic"": ""Or"",
      ""Filters"":
      [
        { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 2 },
        { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 3 }
      ]
    }
  ]
}");
fsql.Select<Region>().WhereDynamicFilter(dyfilter).ToList();
//WHERE id = 1 AND (id = 2 OR id = 3)

https://freesql.net/guide/dynamic.html#%E5%8A%A8%E6%80%81%E6%9D%A1%E4%BB%B6

[hyzx86]

Works great with some front-end component libraries

https://aisuda.bce.baidu.com/amis/zh-CN/components/form/condition-builder#%E5%9F%BA%E6%9C%AC%E7%94%A8%E6%B3%95

[Piedone]

Thanks for sharing! So you're doing kind of embedded queries in GraphQL?

[hyzx86]

Thanks for sharing! So you're doing kind of embedded queries in GraphQL?

I don't quite understand your reference to "embedded queries", I used a ContentHandler to create the full table indexes.

The following code hasn't been updated in a while, in the new version I only indexed the latest version and removed the unreleased version, then inserted the data using the dictionary method The following code uses reflection, which is very low performance

https://github.com/EasyOC/EasyOC/blob/51e3f3690bdc3c0ebf8c42ab1546f83f9d6af1a5/src/Modules/EasyOC.DynamicTypeIndex/Handlers/DynamicIndexTableHandler.cs#L93-L108

Then use FreeSql's own Api to implement the dynamic filtering.

The following is an implementation of PagedContentItemsQuery. It also doesn't require a lot of code since it only needs to query a single table.
https://github.com/EasyOC/EasyOC/blob/main/src/Modules/EasyOC.GraphQL/Queries/PagedContentItemsQuery.cs

To achieve the same effect in OC, you may have to generate some complex query statements

[Piedone]

What I mean is that with dynamicFilter, you have something like a custom query within the GraphQL query.

Any reason you opted for this instead of extending GraphQL directly?

[hyzx86]

Because Yessql's dynamic indexes are too cumbersome, first of all, the business system I'm working on, it needs to communicate with external databases frequently, such as cross-database correlation queries If I use Yessql, I may need to correlate each field with a table.

[Piedone]

OK, so you use a different ORM than YesSql to access the YesSql tables, have a UI query builder to generate queries for it, and you allow GraphQL queries to include expressions for these queries. Do I understand correctly?

[hyzx86]

OK, so you use a different ORM than YesSql to access the YesSql tables, have a UI query builder to generate queries for it, and you allow GraphQL queries to include expressions for these queries. Do I understand correctly?

Yes, exactly.

[Piedone]

OK, thanks!

[hyzx86]

Oh no, it's not a direct access to Yessql's index table, I use another ORM to map some tables to the required types, add the necessary fields to the tables, and these table definitions can be generated automatically following the type definitions.

The Graphql query just queries my dynamically created type data mapping table , and then associates the ContentItemIndex to return the document.

[Piedone]

Ah OK.

[guanwu]

I've introduced this Orm framework into my framework, mainly for implementing full table indexing, but this feature should be available for reference

DynamicFilterInfo dyfilter = JsonConvert.DeserializeObject<DynamicFilterInfo>(@"
{
  ""Logic"": ""And"",
  ""Filters"":
  [
    { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 1 },
    {
      ""Logic"": ""Or"",
      ""Filters"":
      [
        { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 2 },
        { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 3 }
      ]
    }
  ]
}");
fsql.Select<Region>().WhereDynamicFilter(dyfilter).ToList();
//WHERE id = 1 AND (id = 2 OR id = 3)

https://freesql.net/guide/dynamic.html#%E5%8A%A8%E6%80%81%E6%9D%A1%E4%BB%B6

A very interesting solution that can enhance the value in the business field! I shall continue to track the progress of your work.

[Piedone]

Hmm, the documentation is actually wrong, SQL Queries are exposed in GraphQL! However, I'm not sure if this ever worked?

Note how the exception message actually includes the correct list of content items that the query matches.

[gvkries]

It's actually documented and works as described. You just need to check "Return Documents" on your SQL query as well.

But it is unfortunate, that the unsupported queries are exposed in the GraphQL schema, as they will always fail if they do not return documents.

[Piedone]

Ah, I see.