Database Views

Data Service
1

I am having a very basic problem and cannot see what I am doing wrong. I have two related tables and when making a view I only see the two fields from the parent table and am not getting anything from the child table. Screenshot attached.

simpletableview
simpletableview1858×1244 155 KB

3

Hi @David_Duncan ,

Could you please provide your app ID and view name so I an check it with more details?

Regards, Andriy

4

Hi Andriy.
My app id is 45E4C056-E920-A4CC-FFB1-E32F4CF23900
My app view name is Requisitioned

Thanks
Dave

5

@David_Duncan ,

This is an expected behavior.
In your case you selected first columns from Requisitions table and then added other columns from related one. Due to this Requisitions was chosen as main table and you see only data from it since there are no relations to BassComponents.
When you set relations for your Requsitions table records you will be able to see data from related BassComponents records.

Also keep in mind that a view in which used columns from tables bound by 1:N relation will produce Cartesian product. In other words if you have 3 records in parent table and for each of them you have 2 chindren (6 in total in related table) your view will contain 6 entries if no aggregation function or distinct key word are applied (12+12+1*2).

Regards, Andriy

6

Hi Andriy. I am obviously missing something. I set the relationship between the parent Requisitions and the child Basscomponents as 1-N. Why do you say there are no relations ?.

Thanks
Dave

7

@David_Duncan ,

I see that you only defined relationship between the tables but do not set any specific relation objects to your records in the parent table.
In other words, you created a rule that each record of Requsitions can have several records from BassComponents but didn’t set relations itself. To set relations for records you should follow this instructions

Regards, Andriy

8

Thanks Andriy. Your link is - Oops! That page doesn’t exist or is private.

9

@David_Duncan ,

Sorry, my mistake. I have updated link in the previous comment.
Also post it here
https://backendless.com/docs/rest/data_relations.html#creating-relations-between-objects-in-related-tables

Regards, Andriy

10

Thanks Andriy. I think just talking about it seemed to do the trick. I did not make the changes but now my data is showing as expected. I will delete and redo just to see what happens.

Thanks again
Dave

11

@David_Duncan ,

I see that you have added relation for requisition witn number ABD-1234. That is why relation data appeared in the view.

Regards, Andriy

12

Yes - I did it while not understanding it fully. I was under the misunderstanding that the view would include Requisitions in the future but they are only a ‘snapshot’ of existing Requisitions. Is that correct ?. For the Backendless console if I add a Requisition in the Requisition table and some data in the BassComponents I would have to update the view link to have the new data viewed.

I guess I need to explore further as to how it is done for regular data input.

Regards
Dave

14

Hi @David_Duncan ,

I was under the misunderstanding that the view would include Requisitions in the future but they are only a ‘snapshot’ of existing Requisitions. Is that correct ?

No, views work in different way. They are not creating “snapshots” of existing data at the moment of view creation.

You don’t need to update view each time when you update one of the view “source” tables. Each time when you make a request to the view it dynamically assembled to reflect actual data on the moment of query. In other words view represent kind of SQL query which is stored on the DB level and which is used to form data structure “on the fly” when query to the view is made.

We also have a tutorial for views which explains views functionality. Maybe it will help you with your exploration.

Regards, Andriy