Example, we have two tables users and comments.

Requirement: Retrieve each user including id, full_name, ... and the comment that the user has written.

Normaly, we will code like this. But we will facing with N + 1.

select * from users

select * from comments where id in (1, 2, 3, 4, 5, ...)


In PHP, I can code like this. But I dont know, how to code in OutSystems. 

$ users = User :: with (['comments', 'comments']) -> get ();

Please support me. Thank you in advance.

Anh Nguyen Thai Ngoc wrote:

Example, we have two tables users and comments.

Requirement: Retrieve each user including id, full_name, ... and the comment that the user has written.

Normaly, we will code like this. But we will facing with N + 1.

select * from users

select * from comments where id in (1, 2, 3, 4, 5, ...)


In PHP, I can code like this. But I dont know, how to code in OutSystems. 

$ users = User :: with (['comments', 'comments']) -> get ();

Please support me. Thank you in advance.

Hi Anh Nguyen Thai Ngoc,

You can create one aggregate and simply drag both the entity users and comments in the aggregate that will automatically crate join between two. then you can define the join Condition as "only With".

Or you can used Advance SQL widget of outsystems where you can write your SQL to get your expected requirement.


thanks....


Rohan Hanumante wrote:

Anh Nguyen Thai Ngoc wrote:

Example, we have two tables users and comments.

Requirement: Retrieve each user including id, full_name, ... and the comment that the user has written.

Normaly, we will code like this. But we will facing with N + 1.

select * from users

select * from comments where id in (1, 2, 3, 4, 5, ...)


In PHP, I can code like this. But I dont know, how to code in OutSystems. 

$ users = User :: with (['comments', 'comments']) -> get ();

Please support me. Thank you in advance.

Hi Anh Nguyen Thai Ngoc,

You can create one aggregate and simply drag both the entity users and comments in the aggregate that will automatically crate join between two. then you can define the join Condition as "only With".

Or you can used Advance SQL widget of outsystems where you can write your SQL to get your expected requirement.


thanks....


 How about performance if we have over 10,000 record? 

In my view, OS is NG in this case with big data.

 

Hello Yamaguchi.

The only performance issue I see is if you plan to do a IN with thousands of values. A decent SQL query will have a good performance because OS optimizes by only fetching the columns you need and only the subset of rows you show each time.

This is a perfectly good query:

SELECT Users.*, Comments.*

FROM Users

INNER JOIN Comments ON Comments.UserId=Users.Id

Also, 10k is not big data. I'm working with tables that are all in the millions and I don't complain about performance. Just be sure to index them properly and everything will be fine.


Under the Learn section of this site there are several videos about querying data, both in basic training and masterclasses. You should take a look.

Yamaguchi wrote:

Rohan Hanumante wrote:

Anh Nguyen Thai Ngoc wrote:

Example, we have two tables users and comments.

Requirement: Retrieve each user including id, full_name, ... and the comment that the user has written.

Normaly, we will code like this. But we will facing with N + 1.

select * from users

select * from comments where id in (1, 2, 3, 4, 5, ...)


In PHP, I can code like this. But I dont know, how to code in OutSystems. 

$ users = User :: with (['comments', 'comments']) -> get ();

Please support me. Thank you in advance.

Hi Anh Nguyen Thai Ngoc,

You can create one aggregate and simply drag both the entity users and comments in the aggregate that will automatically crate join between two. then you can define the join Condition as "only With".

Or you can used Advance SQL widget of outsystems where you can write your SQL to get your expected requirement.


thanks....


 How about performance if we have over 10,000 record? 

In my view, OS is NG in this case with big data.

 

Hi Yamaguchi,

As Nuno has explained you above for that your requirement if you used the aggregate (because aggregate in OS is most optimized version) that will automatically create an index in entity's. So, If you have indexing on the table then we can't face performance issue.

Thanks.... 


Rohan Hanumante wrote:

Hi Yamaguchi,

As Nuno has explained you above for that your requirement if you used the aggregate (because aggregate in OS is most optimized version) that will automatically create an index in entity's. So, If you have indexing on the table then we can't face performance issue.

Thanks.... 

Please note that creating an index does not happen automatically if the entities are in a different module then the module you are defining an aggregate in. 

Regards,

Vincent

I see some reference here for automatic creation of indexes, as per my understanding it only happens when we have foreign keys with specific delete rules (protect, delete), it will be nice if someone can could shed some light on the other scenarios.

For performance I believe querying 10000 records should not be heavy, but if you want to avoid all that data being instantiated over the screen you could also use Pagination as well.

Cheers

Ashish