P10 Mobile and Advanced SQL Queries

  

Hi OutSystems friends,

I am doing some playing with the P10 Mobile capabilities. 

Unless I am missing it, I am noticing that one cannot create advanced SQL queries to be called from Mobile apps. There is no "Advanced SQL" shape...

I need to execute a join query with a "NOT EXISTS" condition on one of the joined tables.

Is there a way to do this from P10 Mobile?

Thanks for the help!


----Erik

Hi Erik,

You usually can rewrite a "NOT EXISTS" query into a "LEFT JOIN" query to achieve the same results. Example:

SELECT * FROM TableA a
WHERE NOT EXISTS (SELECT * FROM TableB b WHERE  a.TableBId = b.Id)

Can be converted to

SELECT a.* FROM TableA a
LEFT JOIN TableB b ON a.TableBId = b.Id
WHERE b.Id IS NULL

Does this help?

Carlos,

I am aware of this but my testing does not allow me to achieve this result with an Aggregate only. 

I have created a simple eSpace with two tables:

I have 3 items in the FeedItem table. I have one item "excluded" in the HideFeedItem table, by adding it as a row. So the desired query should return 2 rows.

I have a join setup similar to your sql, I believe. But no join type returns the 2 rows I am seeking.

The "Only With" returns 0 rows. 

"With or Without" returns 3 rows:


And "With" returns 4 rows:

Is there something obvious that I am doing wrong here? Or can an Aggregate not do this?

Thanks,

----Erik



Carlos Xavier wrote:

Hi Erik,

You usually can rewrite a "NOT EXISTS" query into a "LEFT JOIN" query to achieve the same results. Example:

SELECT * FROM TableA a
WHERE NOT EXISTS (SELECT * FROM TableB b WHERE  a.TableBId = b.Id)

Can be converted to

SELECT a.* FROM TableA a
LEFT JOIN TableB b ON a.TableBId = b.Id
WHERE b.Id IS NULL

Does this help?



Solution

Hi Erik, 

Try this:

1. Use "with or without" left join

2. Join condition: FeedItem.Id = HideFeedItem.FeedItem

3. Add filter: HideFeedItem.Id = NullIdentifier() 

Hope it helps... 

Solution

That works! Thanks so much!


Harlin Setiadarma wrote:

Hi Erik, 

Try this:

1. Use "with or without" left join

2. Join condition: FeedItem.Id = HideFeedItem.FeedItem

3. Add filter: HideFeedItem.Id = NullIdentifier() 

Hope it helps... 



Glad to help... :) 

Hi Erik,

I'm glad you got your question sorted out. Let me add something to the discussion. Advanced queries are still present in Mobile Apps but they are limited in the sense that you can't execute them on the client-side. To fetch data in a screen using an advanced query you need to use the option "Fetch Data from Other Sources" which is an action flow as you are used to. In this action flow, you can use advanced queries. Also, any other action flow that executes server-side, you can still use advanced queries.

Cheers

André Vieira wrote:

Hi Erik,

I'm glad you got your question sorted out. Let me add something to the discussion. Advanced queries are still present in Mobile Apps but they are limited in the sense that you can't execute them on the client-side. To fetch data in a screen using an advanced query you need to use the option "Fetch Data from Other Sources" which is an action flow as you are used to. In this action flow, you can use advanced queries. Also, any other action flow that executes server-side, you can still use advanced queries.

Cheers

So, to use advanced sql queries on mobile: we can either use "Fetch data from other sources" or call a server action.

The server action will require a REST API call as the server side action will execute on Outsystems frontend and then the results will be returned to mobile device.

The "Fetch data from other sources" will also make a REST API call to Outsystems frontend where the SQL query will execute and then the results will be returned to the mobile device. And I believe we can use this only before the screen renders to bind the UI widgets on the mobile to the data sources.

The "Fetch data from other sources" would not be suitable if say: on every user interaction on mobile UI -  I want to rerun the advanced sql query. Because then we will have to retrigger the logic which "Fetch data from other sources" uses.

I need to rerun a advanced sql query on every user interaction (say the user keeps adding multiple filters) - so I Would have to call the server action in my client action. This will enable me to get filtered data again. However - I see a drawback - that everytime OutSystems will have to make a REST API call for the server action which is being called from the client action.


Hi Chetan,

You are correct, calling a Data Fetch action or a Server Action will do so via a REST call.

Hence, the recommendation is to use Aggregates on mobile, and adapt your Local Storage data model to be in line with your needs (when fetching data from the server you process it and "massage" it into a format that is practical to handle on the client) instead of a direct mapping of your Database data model. This way you will minimize the need for complex queries on the mobile device.