SQL widget - joining an entity with itself

SQL widget - joining an entity with itself



I would like to create a query like this:
FROM {MyEntity} as e1
LEFT JOIN {MyEntity} e2 on e2.[ParentX] = e1.[X]

X and ParentX are of the same type. "MyEntity" is declared as the Output structure.
When I try to test it, Platform says that:
       String was not recognized as a valid Boolean.
But when I take the query from "Executed SQL" tab and try to execute it directly on my database - it gets executed without any problems.

What do I do wrong?

Hi Tomasz,

I would say the order of your query columns results are different from your Output attributes order.
Try specifying your attributes:
SELECT e2.[att1],
A common problem with this is the fact that Service Studio/the Platform doesn't really recognize aliases very well (a major pet peeve of mine). If you specify {TABLE}.*, SS recognizes that it's about the entity TABLE, and expands all attributes in the SELECT. However, with aliases, SS can't do that (well, it could, but it doesn't), and passes alias.* directly to the database. If you have table with more physical columns than the Platform expects (because e.g. you deleted a column in SS and republished), the database returns more data than the Platform expects. In your case it seems that there's a Text column (string) that has been deleted, followed by a boolean column that the Platform expects instead.

The solution is to either explicitly specify the attributes, as Tiago suggests, or remove the deleted columns manually from the database. The downside of the former is that when you later add a column and forget to update the query, again the query breaks. There's no perfect programmatical solution, unfortunately. So, OutSystems, fix the aliases already!!!1!1!11!!! (sorry :)).

EDIT: Of course, if you're on platform 9, and you don't need stuff like subquery's, you could try to use aggregates instead. They do have excelent support for multiple instances of the same table.
As Killian said, Aggregates are the way to go as the OutSystems Platform will handle all the SQL code generation.

If you can't express what you want in an aggregate, you'll need to be careful to match the select columns with the current table definition.
I try to think of Aggregates as being the only option. I do my best to do what I want by just using Aggregates.

However, not always possible.
With Advanced Queries, be careful with aliases and wildcards:
  • There are workarounds to the use of aliases.
  • Regarding wildcards, my suggestion is to never use them.

You may sometimes have a workaround, but if you need to join a table twice, you're pretty much out of options. As for wildcards, you can safely use them if you do not use aliases. We have tables with 20+ attributes, you don't want to write them all out :).


Thank's all for the deep analyse. It was the case of some additional columns in the database.

Yes, I could use aggregates for multiple table instances but in fact I need unions that are not supported by aggregates (as far as I know).

I do join Kilian's request to fix aliases as well as the common request to support cleaning the database after deleting columns (attributes) or renaming tables (entites).

It's already possible to clean up the database of extra unused columns / tables with the DBCleaner_API. I have personally created a component that leverages the API giving it a UI.