Feature request: Allowing more flexible JOINs over shemas

[cmacmackin]

As far as I can tell this is not currently possible, but it would be extremely useful to me. One of the struct datatypes is a Page, corresponding to a page of the wiki. In aggregations it would be nice to be able to work with data from schemas associated with the pages referenced in each row of the table. I imagine this using syntax inspired by value aggregations. My use-case for this would be filtering but possibly it could be used for joining as well (unfortunately the structjoin plugin doesn't seem to support joining based on the Page type).

To provide a motivating example, I'm running a wiki for an organisation which holds lots of meetings. Meetings often generate tasks which someone is meant to complete. I've already created schemas for meetings and tasks (each associated to a separate namespace), where tasks have a field called parent which refers to a page with which the task is associated. This page is often a meeting but doesn't have to be. On the page documenting a particular meeting I've written aggregations which will display all tasks for which that meeting is the parent. This works very well.

We hold some meetings which are public and at which motions may be passed. I've created a schema for motions as well, with a meeting field corresponding to the Page of the meeting where the motion was tabled. In many cases it is more appropriate to associate a task with a motion than with the meeting. However, it would still be useful to know about the tasks arising due to the motions passed at a meeting. Therefore, I would like to put in an aggregation which can filter based on tasks for which the parent page is a motion whose meeting is the current one.

In this particular case there are workarounds (e.g., giving a task multiple parents). However, I feel like such a feature would have many other uses. For example, if date/time filtering can be made to work (#437), it could allow aggregations of all tasks created at meetings during a certain time period.

I'd be happy to contribute a PR to implement this, but as I'm new to PHP and inexperienced with databases I thought I'd raise it here first. Perhaps someone has a suggestion on how to go about implementing it.

[cmacmackin]

Thinking about this a bit more, what I really want is a more flexible way to join schemas. Currently in aggregations you can only perform inner joins using the page. That's a perfectly sensible default, but I wonder if there's a way to extend it. I note that the the cargo plugin for MediaWiki allows doing arbitrary LEFT OUTER JOINs. Maybe adding a new option along the lines of

joinon: { LEFT | INNER | CROSS } <schema_identifier1>.<field_name1> = <schema_identifier2>.<field_name2> [, ...]

The default joinon would be:

--- struct table ---
shema: a, b
joinon: INNER a.%pageid% = b.%pageid%
cols: *
---

That would replicate current behaviour.

In my use-case, as described in the previous post, I'd have

---- struct table ----
schema: tasks, motions
joinon: LEFT tasks.parent = motions.%pageid%
cols: tasks.%title%, tasks.assignees, tasks.duedate, tasks.status
headers: Action, Assigned to, Due, Status
filteror: tasks.parent = $ID$
or: motions.meeting = $ID$
----

More details would need to be worked out, such as how to handle ordering of join conditions and schemas. I think this could be extremely useful, though, and make the plugin a lot more flexible.

[Juergen-aus-Zuendorf]

Extremely useful indeed !!!

[cmacmackin]

Related: #269, #285