dokuwiki-plugin-struct icon indicating copy to clipboard operation
dokuwiki-plugin-struct copied to clipboard

Add custom joins

Open cmacmackin opened this issue 1 year ago • 3 comments

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 into selectCol() (which gets called from select())
  • 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 on AbstractBaseType and refactor it to use getSqlCompareValue() and getAdditionalJoinForComparison() 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 avatar Nov 19 '23 22:11 cmacmackin

@cmacmackin awesome! It will probably take us a while to review this but I appreciate the effort.

splitbrain avatar Nov 21 '23 10:11 splitbrain

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

cmacmackin avatar Nov 30 '23 22:11 cmacmackin

Also, I'm not experienced working with databases, so it is possible that some of the queries could be structured better.

cmacmackin avatar Nov 30 '23 22:11 cmacmackin