Allow sorting by Custom Content field (PHP-side filter and sort support)

[soulseekah]

Biggest reason to have this: this will allow sorting by Custom Content fields or calculation.

---

Forked from #1082

Very often entry values are unsortable in the database without extra special processing and query injection. Very often entry values don't even exist as they are dynamically populated.

Very probably https://secure.helpscout.net/conversation/790197340/19403/ And I'm sure Melissa had something of the sort. And this one #804 is probably something that might benefit from PHP-side sorting.

We can come up with a processing-heavy query backend that filters entries inside PHP from GFAPI::get_entries(), maybe even get_display_value...

Let's see how much traction can be gotten.

[zackkatz]

Prior requests:

https://secure.helpscout.net/conversation/1148735457/26626 https://secure.helpscout.net/conversation/450960053/11638 https://secure.helpscout.net/conversation/606719946/15566 https://secure.helpscout.net/conversation/696357471/17395

[soulseekah]

Filtering

Filtering has to happen after sorting. PHP-side filtering is a subset of SQL filtering. Just like with sorting, all limits have to be taken off in order to keep pagination intact (if LIMIT 20 entries are further filtered - we're in trouble). There are 3 modes of filtering possible:

1. Full PHP filtering, where if a callback exists on the gravityview/entries/filter filter, then all WHERE conditions are removed and filtering is done on the PHP side. Advanced filters (including nested) will need to be replicated correctly unless overridden. This is very useful but difficult to implement, as we need to replicate all SQL WHERE functionality (and casting and whatnot) in PHP. This does not mess with the query much but is resource intensive in the end.

2. Augmented filtering, where if a callback exists, then it's added on top of an already filtered set (in SQL). This will result in a final AND clause that is done on the PHP side. This is the simplest approach. This is a subset of the hybrid approach below, but with (x AND y) structure, where x is the original WHERE condition and y the PHP filter afterwards.

3. Hybrid PHP. This is the most difficult approach. Filtering is done using SQL in place except the PHP-filtered fields, these are not sent as part of the query. Afterwards PHP filters on inside clauses only. PHP-filtered fields are stubbed to a truthy value. This approach is the most performant one, and almost as difficult to implement as 1, but for different reasons. This is a more automated version of augmented filtering.

Augmented filtering seems to be a great approach, since it can allow for quite complex filtering with nested conditionals working as is. A UI is not required for augmented filtering. At least for now. We could theoretically detect dynamic columns like custom_content in the AF and regular UI and kick off Hybrid or Augmented (these are really really similar).

For now gravityview/entries/filter has to be used. This filter should return true or false depending on whether the entry should be part of the collection or not. This should be relatively easy to use by both programmers and non-programmers.

Sorting

Sorting has to happen before filtering. PHP-side sorting will always require all the limits to be taken off. If a limited subset is returned then the sort will only be applied to it and not the whole dataset. There are a couple of other strategies that can be used to sort across a set without getting the set itself. This will get complicated really quickly.

Since filtering also takes limits off in all modes there's no point in inventing a complicated SQL-side sort algorithm. The performance gains will probably not be worth the trouble.

gravityview/entries/sort will have to be used. The filter will be wrapped into an asort callback that will supply the developer with 2 entries to compare, the result has to be -1, 1 or 0.

Update 1

Actually the order of operations in the simple filtering mode (augmented) doesn't really care about whether the sort is applied before or after. Order of operation does not matter here. In the more complex situations filtering comes after sorting, I think.

[zackkatz]

@soulseekah Augmented (option 2) sounds good.

Will this continue to work with joins/unions?

[soulseekah]

Well unions are just a shitton of entries one after the other, they'll have different form IDs; for joins the entries that come back are Multi_Entry objects, so developers can use ->is_multi(), etc. It should work really well. I've given this quite a bit of thought, thus the holdup. But I think I can start coding it this week. Excited.

[soulseekah]

The next step is to make sorting work with Custom Content fields in GravityView by default.

• [x] make it work

[soulseekah]

For the documentation: gravityview/view/remove_filtersorts and gravityview/view/add_filtersorts are the two actions in which PHP filtersorts should be added/removed.

[soulseekah]

So this is pretty much done, @zackkatz I'd like the team to test this out a bit and release as a feature.

[zackkatz]

@soulseekah Great! Can you please test with DataTables too?

[soulseekah]

• [x] make it work with DataTables :(

DataTables is not sending the correct column IDs for sorts, unfortunately. Has to be fixed in the DT plugin templates.

[soulseekah]

A bit of documentation:

PHP Sorts and Filters

GravityView now allows developers to sort and filter queried entries in arbitrary complex ways (non-deterministic! ones, even). A great example of this functionality is rolled out in GravityView core as support for sorting Custom Content fields, which can be as dynamic and random as possible.

Considerations

PHP filtering and sorting is much resource-demanding than MySQL filtering and sorting. Both PHP-side sorting and filtering remove fetch limits and pagination from the underlying entry SQL query, meaning that all entries that are usually displayed on a View in a paged manner will be put into memory.

For example, if your View has 15000 total entries divided into 1000 pages (15 entries each) and you utilize PHP sorting and filtering (manually or by allowing Custom Content sorting), then all 15000 entries will be put into memory. This will result in performance degradation and elevated resource usage, to the point of seeing out of memory errors on your site.

You can switch off Custom Content sorting functionality by using this snippet of code add_filter( 'gravityview/fields/custom/sortable', '__return_false' ).

Use with care.

Development

Every call to View::get_entries will pull the gravityview/view/add_filtersorts hook. This is the best place to inject your custom filter and sort callbacks. Once a fetch is done it's important to remove your filter and sort callbacks via the gravityview/view/remove_filtersorts. This will ensure that View fetches within the same request are not affected by performance bottlenecks.

Both the init and deinit hooks will provide you with the GV\View $view and GV\Request $request parameters.

Filters

Filters should be added via the gravityview/entries/filter hook. This callback will let you remove an entry from the final set of entries for the View. The filter callback should accept 3 parameters: GV\Entry $entry, GV\View $view and GV\Request. You can change the entry object at this stage. If you return null or false the entry will be removed from the collection.

Sorts

Sorts should be added via the gravityview/entries/sort hook. The callback is called from a usort function and will provide you with null, $entry1, $entry2, $view and $request parameters. You will need to return -1, 0 or 1 depending on whether $entry1 is lower, equal to or higher to $entry2.

It is important to understand that once you sort in PHP you are responsible for sorting by all the columns requested. This includes all secondary sorts (and tertiary! sorts if needed).

Examples

GravityView/includes/fields/class-gravityview-field-custom.php GravityView_Field_Custom::add_filtersorts provides a great example on how Custom Content sorting works. How the custom sorting and filtering lifecycle should be adhered to.

GravityView/tests/unit-tests/GravityView_PHP_FilterSort_Test.php is another great place to look for simple PHP filtering and sorting examples.

[bennemann]

https://secure.helpscout.net/conversation/2083076511/45355/

[bennemann]

https://secure.helpscout.net/conversation/2101836833/45847

[bennemann]

https://secure.helpscout.net/conversation/2092656622/45650

[floatytheastronaut]

FreeScout #75763.
https://support.gravitykit.com/conversation/75763?folder_id=4

[mmoga-uf]

_GravityView/tests/unit-tests/GravityView_PHP_FilterSortTest.php is another great place to look for simple PHP filtering and sorting examples.

I can't find this file... has it been moved/renamed?

[bennemann]

https://secure.helpscout.net/conversation/2592671763/54400/

[rafaehlers]

https://secure.helpscout.net/conversation/2748292827/58437

is the following feature coming in future releases : field id for each custom content added into a Gravity View ? If not already considered, I would like to suggest this.

It would definitely unlock a lot of possibilities, if we could call and re-use this content (for example as filtering condition inside the same view, or as [gvfield] output on another post.