How does a!QueryEntity works? I am fetching data from a table through a!que

vipins over 9 years ago

How does a!QueryEntity works?
I am fetching data from a table through a!queryEntity. Table has n number of columns from which only one can have unique data (Primary Key Column). Others can have duplicate data.
I tried to fetch data in batchSize of 5. The problem is, for a column "Name", if a name is same in 12 rows, and I try to fetch data sorted by "Name" for startIndex 1. The identifiers (primary key) comes: 14; 18; 17; 16; 15. Then I change the startIndex to 6, the identifier comes: 19; 18; 17; 16; 15. Now I change the startIndex to 11, the identifier comes: 16; 15; 4; 7; 6.
If you observe, there is some same data coming in every Set (18,17,16,15 -- > 18,17,16,15 --> 16,15). After that identifier changes because same name is there in table for 12 times only.

Can anyone explain why is this happening ?
Why the same/duplicate data is coming even when data is sorted on that column and startIndex is changing?
I am using a Oracle DB. I ha...

Query

Discussion posts and replies are publicly visible

0 vipins over 9 years ago

...ve attached the query as well.

Thanks,
Vipin
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 chetany
A Score Level 1
over 9 years ago

1. About Duplicate data in results: In queryEntity, sorting the data does not mean that it will avoid duplication. You have specified "NAME" as sortField in a!sortInfo, but that does not mean that it will avoid duplication. You may need to use aggregation with grouping on "NAME" column if you want unique results.

2. About BatchSize and StartIndex: BatchSize specifies how many rows from the startIndex need to be returned. So if Batchsize is 5 and startIndex is 10, then it will return records from 10 to 15. BatchSize and StartIndex together control which rows need to be returned. If you have ever worked with SQl queries, you will be aware of the LIMIT clause which can control the number of rows to return. batchSize and startIndex serve a similar purpose
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 vipins over 9 years ago

1. Aggregation is required when I need Unique results for that column, that is not requirement here. Only "Name" column has same entries. All other columns has different entries. So I need all the data.

2. I understand what is StartIndex and Batchsize and that's why I have raised this question that while changing for startIndex...it is giving me the same results....which shows, as per my understanding, that in DB Row 2-5 contains all the same data which is in Row 7-10 (even PK because startIndex has been changed) in my example...which is actually not the case. The data is different.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 chetany
A Score Level 1
over 9 years ago

Do you need data for all columns or data for only the "NAME" column? From your original post, it seems that your table in DB has rows which have same value for "NAME" column. If you want all the data, use batchSize of -1. It will return all the rows from DB.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 chetany
A Score Level 1
over 9 years ago

Use startIndex=1 and batchSize= -1
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 vipins over 9 years ago

@chetany You are not able to understand my problem, leave it. I know how startIndex and batchSize works
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 chetany
A Score Level 1
over 9 years ago

Okay. You mentioned about wanting all the data, so I suggested about the batchSize=-1. Sorting on a column however does not guarantee uniqueness..Maybe I don't understand your problem at all, sorry.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Tyler Criste
Appian Employee
over 9 years ago

I think the behavior you are describing is the same as what was discussed here:
forum.appian.com/.../e-177486

There are some workarounds discussed there. Can you use a query rule in this case instead, passing both "Name" and your table's primary key as sort columns? Query rules allow multiple sort columns while queryEntity does not.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 vipins over 9 years ago

Thanks Tyler, After reading I got its the same issue. I can not use the query rule because it returns all the data which is not needed. Other problem we will face in future is that ,when the data will be more...it may start putting constraint on outputs.
That is why I am fetching the data in batches to display on UI.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rawich over 9 years ago

The problem is that, you leave the order of the records to the database, as sorting by "NAME" field is not sufficient. So the solution is to add additional sorting field to your pagingInfo. Your best bet is the "Id" field, because it's unique.

Something like this:

pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 10,
           sort: { a!sortInfo(
                     field: "NAME",
                     ascending: true),
                     a!sortInfo(
                     field: "Id",
                     ascending: true)
                    }
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel