⭐ [Enhancement]: Enable support for Views with Relationships

[pholcroft]

Currently source entities in a relationship must be a Table as defined here in the RuntimeConfigValidator.

To improve DAB I suggest there should not be this restriction on the source entity being a Table.

[seantleonard]

Hi @pholcroft , thanks for reaching out. Can you elaborate on your use case and what you'd like to see?

[danszot22]

I would like to see this as well.

[peter-chipello]

Example use case would be to create relationship between two Views in Azure SQL. Have two Views with valid primary key fields defined in dab config. Need to create a relationship between a primary key field from the first View and a non-primary key field in the second View. Confirmed that join works without issue in SQL Server. In the DAB relationship docs it does not explicitly state that it is not possible to create relationships between Views, but, after repeated failed attempts, discovered that this is currently not allowed from discussion. Do you know if it is on the roadmap to allow creation of relationships between Views? Workaround is to work directly with the underlying Tables involved, but could save a lot of time if it were possible to create valid relationships between existing Views. DAB is awesome!

[danszot22]

Just to elaborate on my use case, my sql server backend is linked to an enterprise software solution that store values in fields that require some formatting before being exposed. For example, prices are stored without decimals ,11.11 is stored 1111. Also strings are sometimes left padded with spaces " XXXX". The views I created format these values by either dividing by 100 or calling trim function. So it would be nice to relate two views in dab.

[wkelly74]

We are seeing this need too (unless there is a more optimal way of doing this that we are not aware of). We reload our source SQL table daily. We load the data to staging tables and then do a rename of the tables so that they are "active".

However, if we have multiple relationships to a given table we can't rename it from primary to staging because there are other active constraints on it (from other relationships). If we can just use a view and not worry about underlying SQL constraints that would be very nice.

[Benjiiim]

+1 on this one.

For various reasons, the API we would like to implement through DAB is using only views to retrieve data from the database. For example:

• We need to filter out some rows from the results (soft-deleted rows for example)
• We need to filter out some fields that are used for internal systems only
• We need to join multiple tables to construct meaningful entities
• The SQL User we would like to use in DAB configuration has only access to views

Not being able to use views in relationship will make GraphQL useless in our case and will definitely lower the value brought by DAB (removing the "cheap GraphQL compat" benefit of DAB).

[marcusrangell]

Yes. We have the same use cases as Benjiiim, especially filtering rows in the tables due to soft deletes and valid from/to timestamps.

[pingu2k4]

I would like this too. My use case (as I explain in #2094) is that I have a stored proc I want to call.

Lets take the example database in the tutorials - I have added in a stored proc and a view. The view selects all books, and left joins authors. My stored proc then allows me to add in proper pagination, filtering and sorting.

Returning this data, I am now getting multiple rows and DAB is returning multiple instances representing the same book, when they have more than one author. What I really want, is one book with a list of authors. I can perform this transformation myself if I need to, but it would be ideal if instead of performing my data query (paging, filtering, sorting) over a view with the table join, I could simply perform it on the base table, and then have a relationship setup within DAB to go and get the authors separately.

There is no way for me to get the data that I am looking for without doing it var stored proc, which is fine, but then when that data is required to be joined, the inability to setup a relationship between a stored proc and a table is limiting in that I have to transform data once received manually.

[nC-LBR8]

Although this restriction can be circumvented by setting the "type" to "table", it would be more correct if relationships between views were possible. Please provide a solution for this request.