Page MenuHomePhabricator

Support full conditional joins in ExternalData extension
Open, Needs TriagePublic

Description

#get_db_data's join on= syntax does not allow full conditionals to be used. This is similar to the restrictions in Cargo.

The ON portion of an SQL statement can contain anything that is valid in the WHERE portion, at least on MySQL 5.7.

https://dev.mysql.com/doc/refman/5.7/en/join.html

* The search_condition used with ON is any conditional expression of the form that can be used in a WHERE clause. 
Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.

I would like it very much if ExternalData could support this. They are very important whenever you are doing LEFT OUTER JOIN on subsets of data.

I ran into this issue while trying to get around the same limitation in Cargo, and thought that by moving the Cargo tables to a different database (for security), and using the ExternalData extension I could get around Cargo's limitation.

Here's an example query that fails:

{{#get_db_data:
db=wiki-cargo
|from=cargo__contacts=cteam,cargo__contacts=cuser
|join on=cteam._pageName=cuser._pageName,cteam.type='Owner Team',cteam.Username='TeamA',cuser.type='Owner'
|where=cuser._pageName IS NULL
|limit = 50
|data=page=cteam._pageName
}}
{{#for external_table}}
{{{page}}}<br>
}}

The error message I get is:
Error: too many "join on" conditions.

By now you're very familiar with my problem from our conversation in Cargo. Thank you for taking the time to engage me on that.

As I started porting more and more of my corporate wiki to Cargo from SMW, I keep running into situations where this type of conditional is important. Coupled with the limitations of the workarounds in Cargo (only 1 attach) and their increased maintenance cost (extra pre-filtered tables, having to edit multiple templates and create new tables every time I want to do a new query of this kind), it makes sense to me to want to do this using ExternalData even if Cargo is never improved for this functionality.

Thank you for your consideration,
Finn

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 15 2019, 11:49 PM
Aklapper renamed this task from full conditional joins in ExternalData extension to Support full conditional joins in ExternalData extension.Feb 17 2019, 5:22 AM