(REST API METHOD) How to change SELECT * FROM table_name based on UserID

Hi I am new to the OutSystems Environment, I would just like to ask if it is possible to change the REST API URL GET Method based on the UserId? Basically what I am trying to do is show a different dashboard based on the user's account id. Let's say Ben logged in with a UserId = 5, I want Ben to access all sensor data in table5. The REST method would look kind of like this: SELECT * from table5. My initial approach was to create an entity with the UserId set as the primary with its corresponding table name. Then in my REST Get method, I was planning to SET the table name as a variable so everytime I make the query, the user would only get sensor data from his respective table. I'm pretty sure there is a simpler and better solution to this. Any help would be great!

Hi Ray,

You can make the sort of dynamic queries you're thinking if you resort to Advanced Queries with Expand Inline parameters. Basically, when you want to write an advanced query in Outsystems, you'd write something like this:

But what if you don't want to always query Table1, like in your case? What if the inputs that the query receives change the query Entities? You'd do something like this:

If you have a consistent naming scheme for your tables (Table1, Table2, Table3, something sequential), then you can generate the Entity name when your user makes his request, and then you don't have to maintain another Entity that just holds the relationship between UserId and Entity Name. If your Entity names aren't sequential then you'll have to keep using your solution of a lookup Entity.

I want to note that this is a very dangerous use case in Outsystems, and while making things dynamic can be very powerful, it's also very often a double edged sword that makes things a huge pain to debug, since you can no longer deduce what's going on by just looking at your code. Always make sure that you're solving more problems in the present than the ones you might create for the future.

Afonso Carvalho wrote:

Hi Ray,

You can make the sort of dynamic queries you're thinking if you resort to Advanced Queries with Expand Inline parameters. Basically, when you want to write an advanced query in Outsystems, you'd write something like this:

But what if you don't want to always query Table1, like in your case? What if the inputs that the query receives change the query Entities? You'd do something like this:

If you have a consistent naming scheme for your tables (Table1, Table2, Table3, something sequential), then you can generate the Entity name when your user makes his request, and then you don't have to maintain another Entity that just holds the relationship between UserId and Entity Name. If your Entity names aren't sequential then you'll have to keep using your solution of a lookup Entity.

I want to note that this is a very dangerous use case in Outsystems, and while making things dynamic can be very powerful, it's also very often a double edged sword that makes things a huge pain to debug, since you can no longer deduce what's going on by just looking at your code. Always make sure that you're solving more problems in the present than the ones you might create for the future.


Thank you so much for the reply!
The database is stored in InfluxDB and we just use REST API Get method to retrieve them. So if I understood your answer correctly, we just to need to create an advanced query with expand inline parameters but our data is not stored in OutSystems database. Influx uses NOSql, so I am not quite sure SQL queries will work, unless I retrieve all of the data coming from different tables and store them in OutSystems' database and that's the time I'll do advanced queries. Also, I obtain the user's UserId and use it as the primary key to identify which table he/she is supposed to view. Thank you so much again! Hope to hear from you!  

It's going to be trickier then: Outsystems won't be able to directly query InfluxDB (it's not one of the supported database systems).

How do you communicate with InfluxDB using that REST API? I assume it will be something like this? https://docs.influxdata.com/influxdb/v1.7/guides/querying_data/

If your query is an argument in the REST API, all you need to do is build it based on your UserId.

Instead of writing the dynamic query and running it on an Advanced Query in Outsystems, you'd just create a dynamic string, building it according to the UserId, and then send it to InfluxDB through the REST API. You could consider a cache on the Outsystems side to store results if they don't change that often or if it's a lot of data: you probably won't see a lot of performance gains if the data changes constantly or if we're talking about 2-3 results from your query.

Afonso Carvalho wrote:

It's going to be trickier then: Outsystems won't be able to directly query InfluxDB (it's not one of the supported database systems).

How do you communicate with InfluxDB using that REST API? I assume it will be something like this? https://docs.influxdata.com/influxdb/v1.7/guides/querying_data/

If your query is an argument in the REST API, all you need to do is build it based on your UserId.

Instead of writing the dynamic query and running it on an Advanced Query in Outsystems, you'd just create a dynamic string, building it according to the UserId, and then send it to InfluxDB through the REST API. You could consider a cache on the Outsystems side to store results if they don't change that often or if it's a lot of data: you probably won't see a lot of performance gains if the data changes constantly or if we're talking about 2-3 results from your query.

Hello again! We communicate with InfluxDB using REST API with this format 

https://ip:port/query?db=db_name&epoch=s&q=SELECT *  FROM table1

My question is how do you go about creating a dynamic string if we can't pass variables inside the REST API GET Method query?
Did I understand this correctly, when you said dynamic string let's say query = SELECT * from table1
then my get method would be 

https://ip:port/query?db=db_name&epoch=s&q=query ?  

"You could consider a cache on the Outsystems side to store results if they don't change that often or if it's a lot of data: you probably won't see a lot of performance gains if the data changes constantly or if we're talking about 2-3 results from your query."

How do I create a cache to in Outsytems to store results?

I was thinking, if there was a way to actually edit OutSystem's GET method to something like what JavaScript does, like in one of my current projects using AWS textract.

var textract = new AWS.Textract;
textract.analyzeDocument(params,function(err, data)
  {
    if(err) console.log(err,err.stack);
    else Display(data);

  }

);

Where I can edit params and use it as an argument to my GET method. 




Apologies for the long reply,  thank you so much! I appreciate it!



Solution

No problem Ray, it's always good to see someone ask questions about the platform.

You can pass variables into a REST API GET method like this:

Write down the fixed components of the URL, and wrap your parameters in {}. This will tell the platform that those parts of the REST call are dynamic, and you can then create inputs that will feed those values.

Check this article for a primer:

https://success.outsystems.com/Documentation/11/Extensibility_and_Integration/REST/Consume_REST_APIs/Consume_a_REST_API

Solution

Afonso Carvalho wrote:

No problem Ray, it's always good to see someone ask questions about the platform.

You can pass variables into a REST API GET method like this:

Write down the fixed components of the URL, and wrap your parameters in {}. This will tell the platform that those parts of the REST call are dynamic, and you can then create inputs that will feed those values.

Check this article for a primer:

https://success.outsystems.com/Documentation/11/Extensibility_and_Integration/REST/Consume_REST_APIs/Consume_a_REST_API


Hi!This one worked for me. Everytime the user successfully logs in I immediately assign my query variable 'state' to  "SELECT * FROM table"+IntegerToText(GetUserId()), then use 'state' as an input parameter for my REST API GET method. Like you said, this is not scalable for multiple users having one user for each table.  We'll change our schema but for the mean time, this works like magic. Thank you!