WordPress is a good tool, that can handle almost any information and can be used as a framework to build almost any type of WEB sites. And sometimes we want it to do almost impossible things...
As example, we create a classified like site, that stores listings, using "Listify" theme with "WP Job Manager" and "FacetWP" plugins. Those 3 pieces of code are well integrated and work smooth, but this was not enough - client needs a specific sorting and searching results. Nothing to do - that's the needs of his business, no way to change, so must do these search and sorting features "custom, but synchronized with the theme and plugins". We also was need to add "Pods" plugin too, to hold all need info.
FacedWP, when searching for a category, include only "this category" posts. But the list, in our case, was need to be extended with other categories too. We need to add all the categories, to sort them "by category" (and they are need in a specific, admin settable, order) and within each category, ordered by distance, count by geo coordinates (admin settable too). Actually, 3 different lists of results, ordered "globally" together.
To make a custom WP post query is easy, but don't works in our case - plugins do his custom query, to provide the need posts on the list (the FacetWP plugin prepares this "main query", to be exact). And was not possible to get the need "other categories" by plugins - they are not designed for that. Moreover, the theme (by plugins) do pagination too...
So must create a custom SQL query, that returns what we need. The first challenge was to find all need info in the DB, as it is spread between plugins. But "this is another story"... Then the custom SQL query was created. Result: 5 "select" clauses, sub-query 3 levels deep, an "union" clause to form 3 different lists, lot of "inline hacks", all ordered by a single "order" clause with 3 conditions. Not bad... Here is it:
SELECT SQL_CALC_FOUND_ROWS *
FROM (
(SELECT p1.*, 0 as glosor, 0 as gloror, ( 3959 * acos( cos( radians(41.8781136) ) * cos( radians(objlat1.meta_value) ) * cos( radians(objlng1.meta_value) - radians(-87.6297982) ) + sin( radians(41.8781136) ) * sin( radians(objlat1.meta_value) ) ) ) AS distance
FROM wp_posts p1
LEFT JOIN wp_postmeta as objlat1 ON (objlat1.post_id = p1.ID)
LEFT JOIN wp_postmeta as objlng1 ON (objlng1.post_id = p1.ID)
WHERE p1.ID IN (6980,549,6140) AND objlat1.meta_key='geolocation_lat' AND objlng1.meta_key='geolocation_long')
UNION DISTINCT
(SELECT p2.*, fwp.term_id as glosor, 0 as gloror, ( 3959 * acos( cos( radians(41.8781136) ) * cos( radians(objlat2.meta_value) ) * cos( radians(objlng2.meta_value) - radians(-87.6297982) ) + sin( radians(41.8781136) ) * sin( radians(objlat2.meta_value) ) ) ) AS distance
FROM wp_posts p2
LEFT JOIN wp_facetwp_index as fwp ON (p2.ID = fwp.post_id)
LEFT JOIN wp_postmeta as objlat2 ON (objlat2.post_id = p2.ID)
LEFT JOIN wp_postmeta as objlng2 ON (objlng2.post_id = p2.ID)
WHERE fwp.facet_source = 'tax/job_listing_category' AND FIND_IN_SET(fwp.term_id, (SELECT tm.meta_value FROM wp_termmeta tm, wp_facetwp_index fwpi WHERE tm.term_id = fwpi.term_id AND tm.meta_key = 'related_cats' AND fwpi.post_id = 6980)) AND objlat2.meta_key='geolocation_lat' AND objlng2.meta_key='geolocation_long'
AND p2.post_type = 'job_listing'
AND (p2.post_status = 'publish' OR p2.post_status = 'expired' OR p2.post_status = 'acf-disabled' OR p2.post_status = 'private') HAVING distance <= 100)
UNION DISTINCT
(SELECT p3.*, 1000 as glosor, (0 + glor.meta_value) as gloror, ( 3959 * acos( cos( radians(41.8781136) ) * cos( radians(objlat3.meta_value) ) * cos( radians(objlng3.meta_value) - radians(-87.6297982) ) + sin( radians(41.8781136) ) * sin( radians(objlat3.meta_value) ) ) ) AS distance
FROM wp_posts p3
LEFT JOIN wp_facetwp_index as fwp3 ON (p3.ID = fwp3.post_id)
LEFT JOIN wp_termmeta as glor ON (glor.term_id = fwp3.term_id)
LEFT JOIN wp_postmeta as objlat3 ON (objlat3.post_id = p3.ID)
LEFT JOIN wp_postmeta as objlng3 ON (objlng3.post_id = p3.ID)
WHERE fwp3.facet_source = 'tax/job_listing_category' AND glor.meta_key = 'menu_order' AND objlat3.meta_key='geolocation_lat' AND objlng3.meta_key='geolocation_long'
AND p3.post_type = 'job_listing'
AND (p3.post_status = 'publish' OR p3.post_status = 'expired' OR p3.post_status = 'acf-disabled' OR p3.post_status = 'private') HAVING distance <= 100)
) pp GROUP BY pp.ID ORDER BY pp.glosor ASC, pp.gloror ASC, pp.distance ASC LIMIT 0, 35
This is a slow query by any means... and we add some "magic" to speed up it (and that is yet another story...), so if possible, avoid that solution.
OK, how it works?
The "union" clauses provide full list with all the possible results. Doubles are cleaned automatically, first coming goes to the final result, by "union" rules. Still there is the "group by" clause of the main query. Then the ordering begins...
Note the "glosor" alias: it is used for "global sort" of the 3 lists. In the firs "select" subquery it is set to zero, so sorting will put these at top, as it must be since this is the category we search for. In the second subquery it gets the value of the category ID, this "sublist" should be ordered by category ID, those are "similar categories". In the last subquery it is set to 1000, a number way above any category ID, so all the results will "fall down" bellow the two lists. That makes the order of the lists itself, plus ordering of the middle one by categories.
Note the "gloror" alias: it is used for "global order" of categories, admin defined sequence. In the firs "select" subquery it is set to zero, as there in the first list we have only one category. It is not need, actually, but must exists per "union" rules. In the second subquery it is set to zero again, categories there are sorted by the "glosor". In the last subquery it is set to the meta value, that holds the order of categories (a string, that contain an integer number of category ID), as the admin needs them. So categories here are sorted by this number.
And finally, we sort (already ordered) categories in all 3 (already ordered too) lists, by "distance". That number is calculated by the info in two custom fields in each listing (what is a post by WP meaning). Calculation is same in all the subqueries.
So, how to "implant" this in WP?
Easy, you say, use "posts_request" filter (where the full SQL query can be edited). Nope, pagination in FacedWP collects info before it, and don't works (shows zero or the number of posts in "this category" only). The only possible filter (FacedWP creates this query, you remember) just before it is "posts_clauses" (where the pieces of the query can be edited), but it fires before the final SQL query to be assembled. Moreover, the "while" clause is hardcoded to "1=1" (but no "while clause in my main query), and in additional the "from" clause is fixed to "$wpdb->posts" (but my main "from" clause must be the "union" group with subqueries)... And no "having" clause too.
But, fortunately, we have "FROM wp_posts" 3 times in our desired query, so I was able to "slice" my query such way, to be possible to fit it in the allowed fields. I choose to put first 2 "first level" subqueries in "fields" clause, so "from" clause will generate the last one. Here is the result:
$pieces['distinct'] = '*';
$pieces['fields'] = "FROM (
(SELECT p1.*, 0 as glosor, 0 as gloror, ( 3959 * acos( cos( radians(41.8781136) ) * cos( radians(objlat1.meta_value) ) * cos( radians(objlng1.meta_value) - radians(-87.6297982) ) + sin( radians(41.8781136) ) * sin( radians(objlat1.meta_value) ) ) ) AS distance
FROM wp_posts p1
LEFT JOIN wp_postmeta as objlat1 ON (objlat1.post_id = p1.ID)
LEFT JOIN wp_postmeta as objlng1 ON (objlng1.post_id = p1.ID)
WHERE p1.ID IN (6980,549,6140) AND objlat1.meta_key='geolocation_lat' AND objlng1.meta_key='geolocation_long')
UNION DISTINCT
(SELECT p2.*, fwp.term_id as glosor, 0 as gloror, ( 3959 * acos( cos( radians(41.8781136) ) * cos( radians(objlat2.meta_value) ) * cos( radians(objlng2.meta_value) - radians(-87.6297982) ) + sin( radians(41.8781136) ) * sin( radians(objlat2.meta_value) ) ) ) AS distance
FROM wp_posts p2
LEFT JOIN wp_facetwp_index as fwp ON (p2.ID = fwp.post_id)
LEFT JOIN wp_postmeta as objlat2 ON (objlat2.post_id = p2.ID)
LEFT JOIN wp_postmeta as objlng2 ON (objlng2.post_id = p2.ID)
WHERE fwp.facet_source = 'tax/job_listing_category' AND FIND_IN_SET(fwp.term_id, (SELECT tm.meta_value FROM wp_termmeta tm, wp_facetwp_index fwpi WHERE tm.term_id = fwpi.term_id AND tm.meta_key = 'related_cats' AND fwpi.post_id = 6980)) AND objlat2.meta_key='geolocation_lat' AND objlng2.meta_key='geolocation_long'
AND p2.post_type = 'job_listing'
AND (p2.post_status = 'publish' OR p2.post_status = 'expired' OR p2.post_status = 'acf-disabled' OR p2.post_status = 'private') HAVING distance <= 100)
UNION DISTINCT
(SELECT p3.*, 1000 as glosor, (0 + glor.meta_value) as gloror, ( 3959 * acos( cos( radians(41.8781136) ) * cos( radians(objlat3.meta_value) ) * cos( radians(objlng3.meta_value) - radians(-87.6297982) ) + sin( radians(41.8781136) ) * sin( radians(objlat3.meta_value) ) ) ) AS distance";
$pieces['join'] = 'p3
LEFT JOIN wp_facetwp_index as fwp3 ON (p3.ID = fwp3.post_id)
LEFT JOIN wp_termmeta as glor ON (glor.term_id = fwp3.term_id)
LEFT JOIN wp_postmeta as objlat3 ON (objlat3.post_id = p3.ID)
LEFT JOIN wp_postmeta as objlng3 ON (objlng3.post_id = p3.ID)';
$pieces['where'] = " AND fwp3.facet_source = 'tax/job_listing_category' AND glor.meta_key = 'menu_order' AND objlat3.meta_key='geolocation_lat' AND objlng3.meta_key='geolocation_long'
AND p3.post_type = 'job_listing'
AND (p3.post_status = 'publish' OR p3.post_status = 'expired' OR p3.post_status = 'acf-disabled' OR p3.post_status = 'private') HAVING distance <= 100)
) pp";
$pieces['groupby'] = "pp.ID";
$pieces['orderby'] = "pp.glosor ASC, pp.gloror ASC, pp.distance ASC";
#$pieces['limits'] = "";
The "select" clause is hardcode generated, with "SQL_CALC_FOUND_ROWS", if needs (but it is need in such queries anyway).
The "distinct" clause holds my "fields" clause, which is "*".
Then the "fields" clause holds the biggest part of my query, up to the last "FROM wp_posts". It holds inside two of both 3 "having" clauses, unsupported by WP natively.
The "join" clause starts with the alias, need for my subquery, and also the "join" clause of the subquery. Actually, this is the "join" clause of the last subquery itself, but "locked" between alias and closing parenthesis (opening one stay before the "select" clause of the subquery).
The "where" clause starts with "and", which is usual and need technique. It holds the "were" clause of the subquery, but also the closing parentheses of my main query "from" clause and it's alias. Plus the last "having" clause inside.
The "group by" and "order by" seems are the only "WP original" ones, hold the clauses of my main query.
And finally, no "limit" clause is set, so it will not be changed, to be possible for the FacetWP plugin to set it properly to run his pagination.
So WP will assemble "my" query, instead the usual "posts query", and all the limitations of the assembling process are "well used" as "pivoting points". The only missing info is the exactly geo coordinates, IDs of the posts in "this category" etc. All of them actually stay in the original query (and my script "distillates" them from the incoming "$pieces" array), or can be find in DB (my script do this before to modify the "$pieces" array), and placed in the new query. But those minor tasks are not so intriguing...
Why we need so complex single query, can't it be done by 3 easy queries? No way, pagination will not works, needs all the list to calculate pages and create need links...
Can we split the custom query differently? Possible, as there stay 2 other "from" clauses. But I didn't try.
Now the FacetWP plugin thinks he run his query, and all the satellite features works well. WP thinks that this is an usual "posts query" too, and assemble it well. But it's my complex query, "mimicred" to an usual "WP posts query", so the result is what we need. And no changes in WP and the plugin code! :-) Which I am proud with.
Well, was not easy, it was need several days for all this to be done. But it's good that WP can be hacked such way.