Query Helper - One way to achieve a more complex query

I hope some of you may find this useful, this can be complex to utilize or think about, but also can be extremely powerful.

Problem

Often I find myself stuck, not being able to build an efficient query to locate the entity data I am looking for. I have solved this to some extent by utilizing an additional entity type that I call "QueryHelper". I use this entity with the built-in "Configure Link" on the Includes tab. This allows me to combine two or more saved queries, thus resulting in my end result, the results that I need.

Thought Process

Think of this in an order of operations methodology in which each small query is stored in parentheses and returns an individual result. Then we will combine these queries with AND thus only the things returned in all queries are resulting in your includes list:

Entity results where entity IN ((Query 1) AND (Query 2) AND (Query 3))

This can be very stressful on the system though so tread carefully.

-------------------------------------------------------------------------------------------------

Initial Setup (Query Helper entity in control center)

Create a new entity type, I call mine "QueryHelper"
1. This new entity should have link types to Item, Product, Resource, and any other entity you may want to utilize it on.
Makes sure in the control center you add this to the setting to allow as a 'create entity'
Ensure you have "Update Link Rule Definitions" turned on to run. Possibly need to set the Run interval to an increased time to allow larger queries to complete.

-------------------------------------------------------------------------------------------------

Let's start simple - your first query

What we want to solve:

I wish to see all items that were created on a given day, any time on that day.

Solution

I will create two standard queries and save them within the shared work areas and queries

Items created “After Or Equals” a given date

Items created “Before” or on a given date

I will now create an instance of the new entity "QueryHelper"
Using the Includes functionality I will set up a link rule for the "Item”
Note I have used "QH-" to prefix my saved queries which makes it easy for me to filter and find what I am looking for.

I will utilize the "AND" operator
I will be assigning both queries I have created to the link rule.
Thus I am saying "Show me Items (Created After or On 09/01 00:00) AND (Created Before 09/02 00:00)

Save your new Link Rule, now you may need to give it a bit of time to run, this is running both queries in the background and then linking all of those to your “QueryHelper” based on the timing you have your "Update Link Rules" setting configured for. You can also run that process manually from the control center.
This will result in an “Includes” listing of all the resources that are not linked to Product OR Item.

-------------------------------------------------------------------------------------------------
Let's do one a bit more complex

Let us stage this with a scenario:

You have a resource link to Product and a separate link to Item entities. (You could do this with your multiple current links to "Items" and resources as well)

What we want to solve:

Show me resources that are tied to neither Product nor Item

Solution:

I will next create two standard queries and save them within the shared work areas and queries
1. Note that both of these are using a "Not Exists" operator
2. Resources without Items
3. Resources without products
4. I will now create an instance of the new entity "QueryHelper"
5. Using the Includes functionality I will set up a link rule for the "Resource”
  1. Note I have used "QH-" to prefix my saved queries which makes it easy for me to filter and find what I am looking for.
  2. I will utilize the "AND" operator
  3. I will be assigning both queries I have created to the link rule.
6. Thus I am saying "Show me RESOURCES that are in ResourcesWithoutItems AND ResourcesWithoutProducts
7. This will result in an “Includes” listing of all the resources that are not linked to Product OR Item.
8. From here you can open all these in a work area and work with them.

-------------------------------------------------------------------------------------------------

Thank you for reading and I hope this was useful. If you found it useful please like and perhaps we can get better query capabilities in the future.

Tobias Månsson

inriver champion

February 15, 2022 15:08

+2 more

Wow! This is a really creative way of combining queries.

I would say that you don't really need to create an Entity called Query Helper, since most already have the Task entity which should have LinkTypes to basically all relevant entities.

Aaron Jestrab

February 15, 2022 15:36

Tobias Månsson That is an interesting point. I had not thought of utilizing the task entity for this. But I have found it useful to have the separate entity to keep things sorted and separated from other functions.

Thanks for the appreciation on this solution! It comes in quite handy often.

Pontus Sundén

March 01, 2022 09:49

Impressive 🙌🏼 And thank you for the write-up!

Lars-Christian Simonsen

March 15, 2022 07:33

Thank you for sharing this Aaron Jestrab.

We had identified completeness criteria as one way to combine queries, but this is a far more flexible solution.

Also, thanks to Tobias Månsson for floating the idea of using tasks. That let me test and verify this without the need to involve developers :)

Jeremy Peterson (ICP)

October 05, 2022 19:51

Also, the nice thing about having a special entity, is that you can have attributes on the entity to describe what you are doing with that particular helper. If you have many of them, this really helps out.

Additionally, I find it helpful to make a WorkArea folder to hold the queries used for this purpose so they aren't visually clogging up the rest of the WorkAreas.

Join the Easter Challenge!

join the community conversations

Query Helper - One way to achieve a more complex query

Problem

Thought Process