Add properties to queries (i.e. add "TotalCount")

[jacksorjacksor]

Hi there!

We've been exploring DAB usage and have used it for building our root level query objects (i.e. "people", "person_by_pk" etc.).

However, we've been unable to work out how we can add custom logic/queries to these objects - for example, how to create a "total_count_of_people".

Could anyone give us advice on how we should be going about doing this?

TIA!

Rich

[severussundar]

Hi @jacksorjacksor, thanks for the question.

You could create a stored procedure with the logic for calculating the total count of people. This stored procedure can then be exposed an entity in the configuration file, which would enable the execution of a GraphQL query to fetch the total count.

[jacksorjacksor]

Morning @severussundar - ah brilliant, thank you! Do you know if there are any examples/documentation snippets which would cover this process? TIA!

[severussundar]

Here's the Data API builder docs for stored procedures

[jacksorjacksor]

Brilliant, thank you @severussundar - will review and get back to you.

[jacksorjacksor]

Thanks @severussundar - we've tried that and run into an issue, which I've now logged.

[CalvinQuark]

I agree that it would be very helpful if the generated API included filtered table-or-view matching-count endpoints.

In some scenarios, fetching the number of predicate-matching records within a table or a view (the count) is preferable to returning the filtered records themselves.

I started to work around this issue by creating a single generic stored procedure for all of the exposed tables in the database. My intention was guard the execution of that SPROC using the same T-SQL logic that I also use to generate the app-specific entities node in the dab-config.json and staticwebapp.database.config.json files. I later abandoned this idea realizing that I could simply assume that 100 records (i.e., some number) matched the predicate. That assumption proved far simpler and it resolved a grid paging/page-count issue.

Nevertheless, it would be very useful if the generated API supported submitting a complex hierarchical request to a GraphQL endpoint with predicates applied at different levels in the hierarchy and to request a response that returns the number of records which satisfy the predicate hierarchy rather than returning the records themselves.