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