Hello and welcome to the forum, Odyn :)
r3 employs basic SQL, there is no magic. In SQL, if you use grouping, all expressions (ie. everything written in SELECT) must either be grouped or aggregated. The reason being that if you group something, the DB system must know what you intend for each expression ("column" in r3 lingo) to do, if it encounters multiple values in the same group.
The codes _e3, _r2, etc. are aliases, so that r3 knows what to reference. _r referes to a relation (a table in this context), _e refers to an expression (part of the SELECT).
But grouping is usually not what you want in the first place. It can be useful for summaries or calculations, but it´s not particularly useful for displaying nested data or for selection. Especially for selecting a record to open (for viewing or editing), grouping is problematic, because a form can only open 1 record at a time and a group could contain multiple.
There are 3 basic ways to address your need (in general, but also in r3):
- Filter down to one base entity (like
project or author) and then displaying only data relevant to it. In r3 you would have, for example, 1 list of projects. You then click on 1 project to open a form for it. On this form you show another list that shows all authors for this project (and other project related stuff).
- Show an aggregation via sub query. You can have a list of
projects in which you have one or many sub query columns showing aggregated values (like a comma list of all authors for the current project line).
- Produce a matrix. This means that you do not group, but join all relations so that for each combination (of
projects assigned to authors), you get a new line.
For complex data with multiple levels and many relationships, it´s usually best to go with option 1.
Option 2 is useful for overviews. Option 3 is is more esoteric, but can be useful when wanting to filter through all relationships between important entities.
