XF 2.0 Raw Sql joins & XF2's Finder

[Xon]

While XF2's Finder is quite neat, and the ability to use SqlExpression as a where clause covers most angles; using joins can avoid various MySQL-ism related to sub-query performance, and just make it easier to reason about an SQL query. Additionally, it means you can use more columns to drive logic rather than just the single column output of a subquery.

Sadly XF2's query system doesn't allow this, but the underling Finder join structure does permit it. The attached SqlJointTrait.php, injects a couple of functions (and extends resolveFieldToTableAndColumn) to add new methods;

• sqlJoin - define a new job, table, alias and define known columns
• sqlCondition - for a known join alias, rewrite the join condition using XF's join syntax but with support for parsing non-entity columns.

An example is the following;

$finder->sqlJoin('xf_thread_watch', 'threadWatch', ['thread_id', 'user_id'], false);
$finder->sqlJoinConditions('threadWatch', [
    [ '$container_type', '=', 'thread'],
    ['thread_id', '=', '$container_id'],
    ['user_id', '=', $visitor->user_id]
]);
$finder->where('threadWatch.user_id', '<>', null);

Note; container_type/container_id are on a previously joined table.

This shows how to create an SQL-join, populate the join conditions, and then use the alias like a normal entity column with existing Finder bits.

The major changes compared to the existing Finder::join method's condition parsing is the use of columnSqlName when handling all columns when the $ is used. LHS are presumed local columns, unless the $ is used to use locate a column, and RHS is presumed a value unless $ is used to locate a column.

 

[Jeremy P]

Ran into something just the other day that had me wishing there was something like this in the core Really cool, I hope this functionality gets incorporated in a future version.

 

[Xon]

I've been thinking about how to extend a Finder join logic so you can do Entity A => sqlJoin => Entity B as a sequence of joins. There is a couple cases where I think this would logically be simpler than what I've coded up as a replacement.

 

[Xon]

I've released this as an open sourced library with some cleanup.

sqlJoin has a new argument, $hasTableExpr. This allows arbitrary table expressions instead of a table name, but requires rewriting the Finder's join table inside getQuery().

For example;

$finder->sqlJoin('(
    select content_id as thread_id
    from xf_tag_content
    where content_type = \'thread\' and tag_id in (' . $db->quote($tagIds) . ')
    group by content_id
    having count(*) >= '. $db->quote(count($tagIds)) .'
)', 'taggedThread', ['thread_id'], true, true);
$finder->sqlJoinConditions('taggedThread', ['thread_id']);

This finds joins to a thread Finder expression requiring a thread have all of the listed tags (ie an AND'ed list of tags a thread must have)

 

[Banxix]

This is a must have for finder. Without it, extending a finder's query is seriously painful.

 

[Xon]

A number of my add-ons use this, and I've got more like this in a small utilities repo here;

GitHub - Xon/XenForo2-Utils: Standardized utilities that all my XenForo2 Addons use. Replaced by XenForo2-StandardLib repository

Standardized utilities that all my XenForo2 Addons use. Replaced by XenForo2-StandardLib repository - Xon/XenForo2-Utils

github.com

I probably need to better add descriptions, but as the repo is used 'as-is' makes updating it challenging as everything needs to go into new files

 

[Xon]

A number of my add-ons use this, and I've got more like this in a small utilities repo here;

GitHub - Xon/XenForo2-Utils: Standardized utilities that all my XenForo2 Addons use. Replaced by XenForo2-StandardLib repository

Standardized utilities that all my XenForo2 Addons use. Replaced by XenForo2-StandardLib repository - Xon/XenForo2-Utils

github.com

I probably need to better add descriptions, but as the repo is used 'as-is' makes updating it challenging as everything needs to go into new files

I've now packaged this as an add-on;

Standard Library by Xon

A number of helper utilities designed to ease add-on development, does not have any direct user-facing changes. See README.md for additional details. Uploaded to the resource manager to aid dependency tracking and allow updating without updating...

xenforo.com

This allows better versioning, and resolves the issues with checksum conflicts when updating. The next release (v1.1.0) of that add-on will have breaking changes due to type-hinting and cause errors for coding using the various Traits from that Utils repo.