dokuwiki-plugin-struct
dokuwiki-plugin-struct copied to clipboard
Add custom joins
This introduces the ability to JOIN schemas in aggregations ON fields other than the page ID. It required some significant refactoring of the SQL generation so will need extensive review.
Fixes #269, fixes #285, fixes #598
For the time being, joins are only supported on single-valued columns. Multi-valued columns represented another layer of complexity on what is already an extremely complicated set of changes. It would probably significantly delay me finishing this work, so I thought it would be best to get what I currently have merged in. Someone can always add support later.
This work involved the following changes:
- Introducing additional config syntax to specify what fields to join schemas on (plus associated code to process and store this data)
- Setting the "context" for all of the fake columns used by
Search. - Moving logic for handling multi-valued fields to within the
select()method - Moving datatype-specific select logic out of
select()and intoselectCol()(which gets called fromselect()) - Adding a
getSqlCompareValue()method to the datatype classes. This returns an expression for the contents of the column to be used either in filtering or joining. If need be, it will also insert any other conditional expressions needed into a query's WHERE clause. - Adding a
getAdditionalJoinForComparison()method for the datatype classes. This returns information needed to perform any extra joins required when filtering or joining data. Returning the arguments, rather than setting up the join itself, allows the order of joins to be chosen appropriately for the context. - Adding
wrapValues()/wrapValue()methods to the datatype classes. These will wrap a value (or array of values) that is being compared against in any additional logic that is needed (e.g., making it lower-case or converting it to digits). - Implement
filter()only onAbstractBaseTypeand refactor it to usegetSqlCompareValue()andgetAdditionalJoinForComparison()for the type-specific logic. - Introducing a
joinCondition()method to construct alternative JOIN ON clauses when generating SQL queries. - Introducing a
joinConditionIfAdditionalJoin()method to allow any further type-specific logic for complicated joins (i.e., page titles) - Adding logic to a number of existing methods to handle custom join conditions.
- In the
QueryBuilder::addLeftJoin(), choose insertion order based on all tables referenced in the ON clause (not just the left table being joined against). - Updates to existing tests plus writing new tests for the new features (especially joining on page titles, as this is the most complicated case and also likely to be one of the most useful).
@cmacmackin awesome! It will probably take us a while to review this but I appreciate the effort.
@splitbrain I've now finished my work on this. I appreciate that this is a very complicated pull request that will take some time to review. I'm not really in a rush. This was just something I started working on months ago and figured I should try to get it finished before tackling the other issues I recently raised.
I've written tests for new features that I've added but I can't speak for how well the existing test-suite covers some of my refactoring.
Also, I'm not experienced working with databases, so it is possible that some of the queries could be structured better.