Native support for database views

By Kilian Hekhuis on 24 Jan 2012
Quite often, I am repeating the same set of joins in queries (both normal and advanced), that I would ordinarily put in a view. Think e.g. of a CRM system that regularly needs "all active clients" or an ordering system that needs "all open orders". Of course if you need just that, it's easy to put it in an action, but I often find myself needing the result and join that with some other tables.

Unfortunately, for some reason OS does not allow to define database views, which imho is a great gap. Therefore, I'd really like to see support for database views or something similar (e.g. queries you can define on eSpace level and join the result of with another query, but that amounts to the same).
André Ramos24 Jan 2012
While you can't define views in ServiceStudio, you can define them in the database directly and import them with Integration Studio.
Kilian Hekhuis24 Jan 2012
Thanks, I was aware of that, but it has the obvious downsides of having to maintain things outside the platform, which I'd rather not do.
Fernando Sousa25 Jan 2012
May this idea be merged with this one: Create and using Views?
Kilian Hekhuis25 Jan 2012
It'd be good to merge the two, although I think mine has a clearer title and description (all imho of course).
Rebecca Hall5 Apr 2012
This is simliar to what I suggested in http://www.outsystems.com/wisdomofthecrowds/IdeaComment_List.aspx?IdeaId=465.

Maybe votes can be combined.
Kilian Hekhuis6 Apr 2012
Yeah, can we request the merge of ideas somewhere? 41 votes is better dan 21.
Kilian Hekhuis1 Oct 2012
I see they're still not merged, can we request that somewhere?
Philip Prescott13 May 2013
One issue that we have had with using views imported through integration studio is that it will not allow us to use an advanced query that contains both a view and an outsystems table (standard join etc). 

However, if we query that view directly through a database connection, we are able to query both the view and the outsystems table at the same time. 

Having this limitation has pushed us away from using integration studio in many cases. I would love to see this fixed.  
Fernando Sousa11 May 2010
Sometimes you start to have performance issues in database operations (locks, queries time out, too many records, etc.) in you applications and you have to re-think the database model.

One approach could be having flags indicating that a record is deleted (or active/inactive) and hiding it from your users, and remove this records afterwards with a timer then the application has less use.

Having to change all your queries in order to use the flag that you've created can be a headache. You can miss one or two, or having to change complex advanced queries, etc.

It would be nice if one could create a view that would filter records and return only those that did not have the flag marked as deleted and then simply use the Find & Replace Usages functionality to change all queries in your application that once referred directly to the table and now use the view that you've created.

This would help a lot when you have to use this approach and greatly reduce the time developing and testing your application afterwards.

Merged from 'Create and using Views' (idea created on 2010-05-11 14:35:43 by Fernando Sousa), on 2014-03-17 23:00:48 by Gonçalo Borrêga
Kilian Hekhuis7 Mar 2014
This one should be merged with this: http://www.outsystems.com/ideas/Idea_View.aspx?IdeaId=1019 and probably this: http://www.outsystems.com/ideas/465/vritual-entities/

Merged from 'Create and using Views' (idea created on 2010-05-11 14:35:43 by Fernando Sousa), on 2014-03-17 23:00:50 by Gonçalo Borrêga
Rebecca Hall27 Aug 2010
Be able to create an entity that gets its data via a simple query or advanced query.  Then have the ability to use that entity in other queries.  This way you can simplify your queries and also improve application performance.  (For the SQL Server side it would create a view in the database)

Merged from 'Vritual Entities' (idea created on 2010-08-27 15:19:57 by Rebecca Hall), on 2014-03-17 23:01:08 by Gonçalo Borrêga
Ricardo Camacho27 Aug 2010
I believe that is a drill down on the idea of centralized queries I already posted at:

http://www.outsystems.com/wisdomofthecrowds/IdeaComment_List.aspx?IdeaId=446


Merged from 'Vritual Entities' (idea created on 2010-08-27 15:19:57 by Rebecca Hall), on 2014-03-17 23:01:08 by Gonçalo Borrêga
I was going to post a similar idea, but then i found this one, that is a fine approach to what i was thinking. 

I would also suggest that we could use directly the query inside another query (as an entity of course). This could be a way of doing more complex queries just like the usual "nested" queries we can make in SQL.

Another option would be the ability to put the result of a query in a structure and use the structure in another query, but just like a normal entity (allowing joins with other entities and so on). As i can tell the normal use of structures now is like a more advanced variable to be filled at runtime...


Merged from 'Vritual Entities' (idea created on 2010-08-27 15:19:57 by Rebecca Hall), on 2014-03-17 23:01:08 by Gonçalo Borrêga
Kilian Hekhuis7 Mar 2014
What's described above is basically Views, so I think it should be merged with this: http://www.outsystems.com/ideas/Idea_View.aspx?IdeaId=1019 and this: http://www.outsystems.com/ideas/91/create-and-using-views/

Merged from 'Vritual Entities' (idea created on 2010-08-27 15:19:57 by Rebecca Hall), on 2014-03-17 23:01:08 by Gonçalo Borrêga
In the entities section of Service Studio we could create\manage views.
They could be defined like an Advanced query, and with the 1 click publish the platform would generate the view in the platform database. 

PROS:
1) Don't need to create views directly in the DB Server to import them later with Integration Studio;
2) Changes to views would be easier and faster;
3) The views would be validated with the TrueChange technology, and any entity change would be identified;
4) Less errors;


Merged from 'Create\Manage views from Service Studio' (idea created on 2014-12-05 11:43:08 by Carlos Henriques), on 2017-11-24 18:33:53 by Gonçalo Borrêga
Andrew Burgess8 Dec 2014
This would also help to remove "repeated" code in your applications. For example, if you have the concept of "customers with active subscriptions", you might need to use that to populate data for a list screen, but also as the basis for further queries in the business logic, such "customers with active subscriptions that are in arrears." Currently, the filters required for "customers with active subscriptions" would need defining twice. That's wasted effort, but is also a pain if the definition changes - it has to be fixed in many places.

Merged from 'Create\Manage views from Service Studio' (idea created on 2014-12-05 11:43:08 by Carlos Henriques), on 2017-11-24 18:33:53 by Gonçalo Borrêga
Alexei5 Dec 2016

It would be great if we could not only create Entities but Views also.

At the Data Tab -> under database make a segregation of Entities (Tables) and Views.

Database

 1. Entities

  2.Views

Provide the same option to make the views public  (YES/NO).

Make it possible to use the views in both query options ( Aggregate / Advanced Query)


Benefit:

This can alleviate a lot of creation of complex queries with a lot of joins in the Aggregate and  Advanced queries. Queries)




Merged from 'Create Database Views through OutSystems' (idea created on 2016-12-05 14:19:57 by Alexei), on 2017-11-24 18:31:32 by Gonçalo Borrêga

Merged from 'Create\Manage views from Service Studio' (idea created on 2014-12-05 11:43:08 by Carlos Henriques), on 2017-11-24 18:33:53 by Gonçalo Borrêga
Justin James6 Dec 2016

Agreed. The View4Entities component is good, but fills a bit of a different role than this, and you'd still need to add the views back in through an Extension... AND you cant do joins, filtering, etc...

J.Ja



Merged from 'Create Database Views through OutSystems' (idea created on 2016-12-05 14:19:57 by Alexei), on 2017-11-24 18:31:32 by Gonçalo Borrêga

Merged from 'Create\Manage views from Service Studio' (idea created on 2014-12-05 11:43:08 by Carlos Henriques), on 2017-11-24 18:33:53 by Gonçalo Borrêga

Ability to create (filtered) views and reuse them as a kind of entity. 

On the view you could define CRUD using actions (or restrict when absent)

Usage could improve security and maintainability. For instance; the using party only sees the views and not the entities, which could be different.

This would also improve the usage in connecting to legacy systems. You do not have to replicate or expose the original entity, but you could encapsulate it within a view. 

Single source of Truth, but adapted to your use



Merged from 'Create/Manage (filtered) Views on entities (or saved Aggregates)' (idea created on 2017-01-20 10:37:38 by Patrick Baanvinger), on 2017-11-24 18:34:30 by Gonçalo Borrêga

This has been suggested several times, most recently here -
http://www.outsystems.com/ideas/2771/create-database-views-through-outsystems



Merged from 'Create/Manage (filtered) Views on entities (or saved Aggregates)' (idea created on 2017-01-20 10:37:38 by Patrick Baanvinger), on 2017-11-24 18:34:30 by Gonçalo Borrêga