Complex queries

  
hi everyone!

ive got three related questions, regarding queries, and would like to know what would be the best approach:

- i have a bool value and if its true i want to retreive all entity records, where the according attribute is also true
but if its false, i basically want all records, where the attribute is true or false

- then i have an entity with various references to other entities (always via ID)
where i only want to query certain records, matching a complex logic (involving further queries, checks etc)...
prefferably as reuseable as possible, since its needed at several places and if something changes, i dont necessarily want to go search what i need to adapt.
but i have the feeling, that using an action to get the data for a record list, wont be the ideal solution for screen processing

- third and final, how do you handle parent entity records (ie. record1 is parent of record2 and 3 etc)
without having to select 1 hierarchy after the other and insert into the according position of the record list
but still get all records in the right order

what would you recomend?

thank you!
Hi Enigma,

I don't really understand question 2 and 3. Please rephrase with some kind of example.


I think I do understand the first question. I am still learning myself, but this is what I would do: 

The simple solution is to use an "if" in your preparation, and use 2 queries with different "where"-statements (in advanced queries), or just add a condition in simple queries. In your case one with a condition (yourBooleanValue=True), the other without.

A more elegant solution would be to create an input parameter "yourBoolean" and place this input parameter in your advanced query ( "where YourEntity.[BooleanValue]=@yourBoolean). Before that query I would create an if statement (if: Boolean = False), and after that I would use an assign to assign nullidentifier to the parameter (yourBoolean = nullidentifier ()).(only if YourBoolean=False).

I hope this helps,

Cheers, Rienk Eisma
thenk you rienk, your second suggestion is what im currently doing, but love/preffer your third suggestion, cheers!

regarding the 1ast two questions, heres two examples:
- you have an account's table for accounting, and want to display the available accounts to the user.
these accounts can be assigned to the company and/or department and/or user,
while the user can also be in different companies and/or departments.
depending on which companies/departments/etc the user belongs to, he/she can see more or less accounts.

- for the 2nd lets take something that every one knows, a directory structure,
lets say we have an entity with a list of directories identified by the entity ID,
and we have a 2nd field containing the ID of a further record of the same entity.
if directory2 is in direcotry1, then directory2 would have 1 set as its parent.
currently if i perform a simple select, you would get a list of everything in a random order.
to get everything in the right order, i currently have to select directory1 first and directory2 in a second select etc
Hi,

For the first question ...I dont see the need for an advanced query for that requirement (unless the query itself needs it).
Wouldnt a simple condition do it? or did I missread it?

"BooleanInput = False or entity.attribute = True"


As for the rest ill see if I get time to read it more carefully tomorrow.

Regards,
João Rosado
Hi,

Yes a simple condition could do it if an advanced query isn't needed.


The second question also seem to be parameter/condition issues. 

In the second question it seems to me you have to create a simple query including all those entities (I presume these entities are related). After that you create a parameter UserId and use it as a condition. (If, in  your simple query, entities are not joining properly you could try to fiddle with the sequence in which you add entities to the query.)

The third question I still don't understand but at first glance I it seems that it can be solved by focussing on  OrderBy.

Cheers, Rienk Eisma
Another tip regarding the first query:
You can simply add that boolean parameter to the query, mark its property "Search Parameter" to true, and do the condition just as Entity.Attribute=BooleanParameter.
When the parameter is a Search Parameter, when it is empty, the condition is discarded by the platform. For booleans, if false, the condition is discarded... which means that you'll have the expected result:
 - when false, no condition is applied and all results are returned
 - when true, the condition is applied, and only results where Entity.Attribute = true are returned.

For the 2nd question... the best option is really to encapsulate that logic into an action, and return a recordlist with the information... You'll loose some of the optimization done by the platform (if a field is not used in a screen, the platform doesn't even puts it in the query), but if the logic is complex enough for you to consider factoring out to a single action, you're probably have other benefits (maintainability). It is a tradeoff... depending on your specific logic...

As for the third question, the relational model always has this challenge. I usually handle it in one of two ways:
 1. When storing the data, ensure that I have an "order" attribute that is filled by my logic when creating records. When adding a level 2 record, update its order to be higher than its parent (and higher than other sibling in the same level), and then update all remaining records to have their order = order + 1. That way, you always have a sorted list in the database, and the query to get the full list to fill a tree for example, is just a simple query ordered by the order attribute.
 2. Another option is to use an hierarchical query to load the data. In SQL Server this is done via Common Table Expressions (CTE). It is basically a recursive query that allows you to get hierarchical data in a "flat" way. I usually use the example at http://www.sqlteam.com/article/more-trees-hierarchies-in-sql to guide me in doing those queries...
Just pay attention to a possible performance penalty you may have if there are huge amounts of data. The 1st approach scales better when there are a lot of reads and few updates. The second gives you less work on the development of the create/update logic, but if the table is very big or there are very reads, it might not scale that well. Depending on the use case, you may consider applying cache to that query in the platform (usually for a menu that is loaded to a tree, that is a very reasonable approach)

Hope it helps.
Gonçalo
João Rosado wrote:
Hi,

For the first question ...I dont see the need for an advanced query for that requirement (unless the query itself needs it).
Wouldnt a simple condition do it? or did I missread it?

"BooleanInput = False or entity.attribute = True"


As for the rest ill see if I get time to read it more carefully tomorrow.

Regards,
João Rosado
 
 a simple query does the trick =)
already noted; where possible always use simple, only if there is no other way use advanced queries
Rienk Eisma wrote:
Hi,

Yes a simple condition could do it if an advanced query isn't needed.


The second question also seem to be parameter/condition issues. 

In the second question it seems to me you have to create a simple query including all those entities (I presume these entities are related). After that you create a parameter UserId and use it as a condition. (If, in  your simple query, entities are not joining properly you could try to fiddle with the sequence in which you add entities to the query.)

The third question I still don't understand but at first glance I it seems that it can be solved by focussing on  OrderBy.

Cheers, Rienk Eisma
 
for the 2nd ive already tried a simple query, but it runs into problems when using multiple companies/departments, since i get the required value multiple times.
and for a select dinstinct it would require using an advanced query, and endup with a slight poker, since im not 100% sure i will only get the valid values.
Gonçalo Borrêga wrote:
Another tip regarding the first query:
You can simply add that boolean parameter to the query, mark its property "Search Parameter" to true, and do the condition just as Entity.Attribute=BooleanParameter.
When the parameter is a Search Parameter, when it is empty, the condition is discarded by the platform. For booleans, if false, the condition is discarded... which means that you'll have the expected result:
 - when false, no condition is applied and all results are returned
 - when true, the condition is applied, and only results where Entity.Attribute = true are returned.

For the 2nd question... the best option is really to encapsulate that logic into an action, and return a recordlist with the information... You'll loose some of the optimization done by the platform (if a field is not used in a screen, the platform doesn't even puts it in the query), but if the logic is complex enough for you to consider factoring out to a single action, you're probably have other benefits (maintainability). It is a tradeoff... depending on your specific logic...

As for the third question, the relational model always has this challenge. I usually handle it in one of two ways:
 1. When storing the data, ensure that I have an "order" attribute that is filled by my logic when creating records. When adding a level 2 record, update its order to be higher than its parent (and higher than other sibling in the same level), and then update all remaining records to have their order = order + 1. That way, you always have a sorted list in the database, and the query to get the full list to fill a tree for example, is just a simple query ordered by the order attribute.
 2. Another option is to use an hierarchical query to load the data. In SQL Server this is done via Common Table Expressions (CTE). It is basically a recursive query that allows you to get hierarchical data in a "flat" way. I usually use the example at http://www.sqlteam.com/article/more-trees-hierarchies-in-sql to guide me in doing those queries...
Just pay attention to a possible performance penalty you may have if there are huge amounts of data. The 1st approach scales better when there are a lot of reads and few updates. The second gives you less work on the development of the create/update logic, but if the table is very big or there are very reads, it might not scale that well. Depending on the use case, you may consider applying cache to that query in the platform (usually for a menu that is loaded to a tree, that is a very reasonable approach)

Hope it helps.
Gonçalo
 
precisely what ive done, or how i already understood rienk =)

for the 2nd, it seems that i dont have a better option then (gets irritating having to update the record list from an action on every refresh)
but atleast i know im not on the wrong path, thank you =)

for the 3rd, i probably preffer your 2nd tip, since i will probably run into problems updating the order of parent records, which a user might be editing at the same time.
regarding the performance penalty, does this affect entities with a lot of records and/or columns?
i presume it will still be faster than creating a recursive logic, that will perform a simple query multiple times?

thanks to all for your help!
enigma wrote:
regarding the performance penalty, does this affect entities with a lot of records and/or columns?
i presume it will still be faster than creating a recursive logic, that will perform a simple query multiple times?
 
Affects when it has a lot of records. Columns don't make that much of a difference
Yes, it will definitely be faster than creating recursive logic :)
Alright, thank you very much guys!
you where a huge help