[Qgis-developer] Table joins

[Hugo Mercier]

Le 25/11/2013 15:18, Matthias Kuhn a écrit :
> 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.

Yes, exactly. The "left" part of the join will always be fixed.

> 
> 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).
> 

For the aggregate functions, we do not have this kind of things for
regular, "unjoined" tables, so we won't have it for joined tables.

We could stick to the current behaviour of QGIS joins for now : keep
rows of the "left" part and get the first row (or null) of the right
table(s).

>>
>> 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.

You're right. We would have to specify somewhere what relations we would
like to see in a join.

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

The only issue I see is fields from different tables that may have the
same name. So either we need some kind of "." prefix, or we need a way
to declare a remapping of names.
And this should be applied to the different $geometry fields of the
tables involved in the relation.

> 
>>
>> 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.

Relation properties can be used to define the type of join you want
(left, cross, "qgis old join style", etc.) and the predicate to use (==
by default, or an expression with "intersects()" or whetever you like).

Then in the layer's properties, you could define what relations are
"seen" by this layer, and so what fields are available to the attribute
table.

And then, we could tag the old "Join" functionality obsolete.

-- 
Hugo Mercier
Oslandia