[Qgis-developer] Table joins

[Matthias Kuhn]

Hi

On Mon 25 Nov 2013 14:10:27 CET, Hugo Mercier wrote:
> Hi all,
>
> I have some questions regarding table joins in QGIS.
>
> Looking at the GUI, there are two locations where such "joins" can
> currently be declared :
> - through the "Joins" tab of a layer's properties (1:1 join)
> - through the "Relations" properties of the project (1:N joins)

A join isn't really 1:1, it's also 1:N.
E.g. two tables with buildings and city, where each building is in a 
city but a city can have several buildings. You can easily join the 
city to the building, which will make the city show up in the attribute 
table of several buildings, but it won't work the other way around.

The difference I see personally at the moment is, that a relation is 
sort of a "dynamic" (bi-directional) thing and a join is rather "hard" 
and one-way.

>
> Relations are very cool when used in edit mode on a layer (see
> http://blog.vitu.ch/10112013-1201/qgis-relations).
>
> But nothing appears in the attribute table, there is no new columns
> (which is the case for a "Join" definition), so I guess one cannot use
> "related" fields for symbology, labelling, and so on (even with some
> assumptions on the relationship, like that is really a 1:1 relationship)
>
> This seems a bit confusing to me, because they seem very closely related
> concepts (and well-known concepts in databases), I find strange to have
> them defined at two locations.

How they are shown in the attribute table should be a users choice. 
Sometimes a relation can just be a value list, so it should rather show 
up as a ValueMap widget instead.
Or it could be that you actually wanted to show an "array of linked 
rows" (show me a list of all the buildings in this city) or some kind 
of group operation on it (max(b), avg(b)) which would be a "calculated 
field"
or as we are used to from the joins "show me the row referenced by the 
foreign key column on this table" (show me the city of this building).

>
> I did not yet have a look at the code, but are there any chance we can
> treat fields of a "relation" as first-class fields in a layer ? The idea
> would be to use them to define 1:1 (current "joins") and get rid of
> layer's "joins".

As relational models can get rather complex, just showing related 
records is not always what we want. Sometimes, the related table has 
another relation to another relation and this can get very complex (and 
time-consuming), so I would rather opt to have some switch about which 
relations we should treat as first-class fields of a table.

For the expressions, there should be a syntax introduced for joining 
other tables / resolving relations.

>
> We are also asked for the possible addition of spatial joins within
> QGIS. I would find it nice to be able to define them as "relations".
> Probably starting with a 1:1 "intersects" join, but with an open
> possibility for 1:N spatial joins (contains, etc.), and why not free
> custom user expressions as join predicates (including "left" and "right"
> geometries for instance)
>

Spatial joins definitely a nice idea, I hope we can get to this one 
day, but I didn't think a lot about it yet.

When it comes to the API I think the joins (outer, inner, left, right) 
is a rather easy call to relation object returning an iterator. What I 
am not sure is about an appropriate UI where the user can define such a 
thing as a join so the attribute table shows the resulting table.

Best
Matthias