🧲 The QUERY Column: One Column to Rule Them All!

I’ve experimented a little further, here’s how far I got:

  • Parent-to-children Relation. I got the same output with a Query.
  • Children-to-single-parent Relation. I got the same output with a Query.
  • Children-to-many-parents Relation. I got the same output with a Query.
  • Self-relation to create a unique list. I got the same output with a Query.

For those use cases which seem to be the main ones, Relation is included in Query. Meaning I might as well ditch Relation. Relation seems to be redundant now.

This does not take into account performance considerations.

1 Like

I might still use a self-relation to determine the unique rows of a table (along a given column). For instance if name has a, b, c, a, b, c, then the output on the first three rows would be a, b, c (self-relation with single match, lookup.RowID, if-the-else where lookup.RowID == RowID).

1 Like

Exactly. Querying IS SUPER powerful, but querying a huge table and using lots of conditions won’t be AS efficient as creating the relation first and then querying the relation…but then you’re using 2 computed columns, so it’s a trade off I guess.

1 Like

Further observations:

  • Clearly (I hesitate to use this term) Query is meant to replace Relation. Query is more powerful than Relation, it’s somewhat easier to set up, it’s more logical, Relation is included in Query. The only issue is that Relation is still quicker than Query, which is an issue if one would like to use Query only instead of Relation.

  • We now have two computed columns that are extremely similar, it feels messy. It’s like those 4 title components that are basically 1 single component, or card and grid that are basically 1 style.

  • I haven’t tried it yet, but I don’t think I would want to query a relation. Since Relation is included in Query, that would be like querying a Query. That doesn’t feel right.

Fair assessment!

I’m only going based off of what Glide is telling us. If they say a relation is faster than a query, I have to believe them.

All this to say, I’m sure improvements will be made to query, and perhaps it will eventually replace relations. Who knows?!

3 Likes

So what would be less arbitrary?
The third from the start?
The second last one?
The one in the middle?
What if there was only one match?

This is a common pattern in computing. As soon as you find a match, exit the loop and move on.
Think about the way that the if-then-else column works, it’s exactly the same. The first condition that matches determines the result :slight_smile:

3 Likes

Phrasing it like you did makes so much more sense to me. I never saw a single Relation as an iterative loop but rather as a vertical scan. Perhaps these are the same but loop plus find match plus exit loop makes more sense to me. Programming never ceases to amaze me.

What I did to achieve this with Query was filter to find all (since Query is a match multiple) then limit to 1 (first from the bottom or top). With Query we seem to lose this ability to exit the loop when a match is found.

I like Query a lot.

2 Likes

Hola!

When I read the term “self-relation” something strange happens in my eyes :woozy_face:

In my point of view, a self-relation (also called self-join) should be avoided and not used as much as possible.

A self-join is a join in which a table is joined with itself, and when used, generally indicates that the table is non-normalized in 90% of cases.

This may not be a problem for a small table (perhaps less than 2k-3k rows), but using a large, complex table with higher concurrency (moves) will lead to poor performance and degrade your query response time. It is a fact!

Google even states that this trick should be avoided if you are going to use their BigQuery database, which is column-based and not row-based like traditional RDBMS databases (what we use in Glide).

Is a relation faster than a query? I think so and the reason is because the Query column needs to classify and discard the data based on the filters set on it. The query has more work and calculations to do than a relation.

Instead, the relation simply joins 2 tables using a (common) key field/column between them and creates a new and larger virtual table ready to be parsed.
We could have had a better tool if we could set Query to the fields/columns to use and not just the filters.

I mean, now we are using a standard Query clause like this in Glide:

Select *
From MyTable
Where Filter1 AND|OR Filter2 AND|OR … FilterN
Order by Column … ASC|DESC;
Limit N

but it could be better if we can get the following:

Select Column1, Column2, Column3
From MyTable
Where Filter1 AND|OR Filter2 AND|OR … FilterN
Order by Column … ASC|DESC;
Limit N

A query with few columns and no filters will be faster than the current Query column or even the current Relation column.

Can we have this enhancement for the next Query version?

Saludos a todos!

5 Likes

@MattLB @Robert_Petitto This sounds interesting…
Could you elaborate?

Sounds like something which could be super useful for building reports In general without having to add many columns or tables.

I agree, Query is practical for cohort analysis, i.e. reports. With Relation, reporting requires a Template column, a self multiple Relation and a Rollup. With Query, reporting requires a Query and a Rollup. Query is much easier to understand than Template + self Relation.

If you need to create a traditional Relation from a query, you can always using Single Value to pull in the first queried item’s row ID, then create a relation using that. Not hard to figure out but just a tip I came across.

2 Likes

Actually, single value → Whole row would accomplish this in one step.

3 Likes

Does the query column use updates? Know queries to big tables do and am curious if this works at all in the same way. Thanks so much for your help!

It shouldn’t. It’s just another type of computed column, like a relation.

3 Likes

Haven’t tested this extensively, but having a sort option in query, combined with the sort option in a component allows for secondary sorting. So you can achieve this:

Apple 1 oz
Apple 2 oz
Banana 1 oz
Banana 3 oz

etc.

Actually, I think the sorting in the component would override the sorting in the query.

1 Like

Might be just an anomaly of this example, but let me show you what I’m seeing.
So here is the sheet order:

Here’s how it displays if the query is sorted by Number, but the component has no sort.

If I then add sort by Name to the component I get this:


Which appears to be successfully sorting all of the ABs first, then within that sorting by the number, then BC then SB, etc…

2 Likes

I stand corrected!

1 Like

I wonder what’s the best method to use Glide for many reports without

  1. Resorting to separate tables per report.
  2. Keep reports columns separate of the Base data tables/columns so to keep the backend simple.

All that- while maintaining a consistent and logical structure for report building.

I think 1. and 2. are almost mutually exclusive.

My practice is to create a separate table for each set of reports/charts. For example:

This achieves the goal of keeping the data tables as clean and uncluttered as possible, but it also creates a logical separation of concerns and makes maintenance/debugging much simpler. Because each report has it’s own table, I know exactly where to look if something breaks.