Virtual Primary and Foreign keys?

Schema & Data
1

Hi,

getting started using Dreamfactory, looks great. Using MS SQL as source, having two related views without any primary and foreign keys; how can I make the relationship between the two tables without the possibility to change the schema on the database itself ? Is it possible to make “virtual” primary and foreign keys for Dreamfactory only, Tried to mark a field on the a table in the Dreamfactory admin UI, but it tries to set on the database as well ?

Any ideas ?

Thx for your input in advance :smile:

\Thomas

2

Good timing, this feature is about to come out next week (first on GitHub followed closely by Bitnami packages and Docker Hub). The docs at wiki.dreamfactory.com will explain how it works.

In summary, here’s what’ll be supported next week:

  • GET using virtual foreign keys across two tables on same db
  • GET using virtual foreign keys across two tables on different dbs
  • Aliases for virtual fields and relationships
  • Virtual fields for db functions like appending two string fields such as concat(field1, field2)
  • Virtual fields for aggregation by field name – sum, min, max, count, etc.

UPDATE support will not be in the next release but will be added shortly in the next sprint ending Dec 20.

3

Hi Ben,

it’s all about timing :slight_smile: looking forward using the new feature. Only need to make GET’s, so just spot on…

Thx again.

\Thomas

4

Ben – How do you go about setting the Virtual Field. example – if you are setting vcol1=SUM(payamt). Where do you specify this?
Rajiv

5

@rajivperera Here is the wiki link for the documentation. Easiest way, is to go the “Schema” tab on the Admin application of your instance, create a new field for the desired table, select “virtual” for the field type and enter a name and SUM(payamt) part goes in the DB function area near the bottom. Since this is an aggregate function, make sure you select aggregate checkbox. When you make a call needing the aggregate results just do GET /api/v2/<service_name>/_table/<table_name>?field=vcol1.

1 Like
6

Thank you Sir. This is very helpful. :slight_smile: