|
Ruben
|
July 11, 2017 at 10:39 am
I had a working extension for my theme’s featured slider, to show specific EE events. This stopped working after an update, so I went to investigate.
Related thread
I receive the following database error:
[Unknown column 'sj5h_esp_datetime.DTT_order' in 'where clause']
SELECT SQL_CALC_FOUND_ROWS sj5h_posts.ID FROM sj5h_posts WHERE 1=1 AND sj5h_esp_datetime.DTT_order = 1 AND sj5h_esp_datetime.DTT_EVT_end > 1499787959 ORDER BY sj5h_posts.post_date DESC LIMIT 0, 8
sj5h_ is my WP prefix.
What seems to be the problem is that the sj5h_esp_datetime table is not included in the FROM clause, but I seem to be unable to get it in there.
I attempted to use the following function to add it:
function event_espresso_listing_join() {
return ' INNER JOIN ' . EEM_Datetime::instance()->table() . ' ON ( ' . EEM_Event::instance()->table() . '.ID = ' . EEM_Datetime::instance()->table() . '.' . EEM_Event::instance()->primary_key_name() . ' ) ';
}
and I added this to the query:
add_filter( 'posts_join', 'event_espresso_listing_join');
I even tried to return this in the function:
' INNER JOIN sj5h_esp_datetime ON (sj5h_posts.ID = sj5h_esp_datetime.EVT_ID) ';
but to no avail.
All of the above cause 500 errors, not even showing a php error anymore but just part of the page until it gets to this part.
I used code from this page
Not sure if this will fix the problem, but either way, can anyone get me in the right direction for this fix? Thanks!
|
|
Josh
|
July 11, 2017 at 11:21 am
Hi Ruben,
The code from the reference has a number of important checks before it adds the extra WHERE and JOIN, and it also passes in the full SQL (e.g. line 18) then adds to the SQL (on either lines 21 or 23), then returns the full SQL with modification (line 25). Your code is wiping out the SQL completely then replacing with a portion of SQL.
If you follow the code example very closely you’ll get back on the right direction.
|
|
Ruben
|
July 12, 2017 at 5:49 am
I’ve taken a closer look, and I’m still getting nowhere.
Currently the code is as follows:
function featured_event_espresso_listing_join( $SQL ) {
$dttable = EEM_Datetime::instance()->table();
$evtable = EEM_Event::instance()->table();
$evtablepk = EEM_Event::instance()->primary_key_name();
$SQL .= ' INNER JOIN ' . $dttable . ' ON ( ' . $evtable . '.ID = ' . $dttable . '.' . $evtablepk . ' ) ';
return $SQL;
}
add_filter( 'posts_join', 'featured_event_espresso_listing_join' );
function featured_event_espresso_listing_where( $SQL ) {
$dttable = EEM_Datetime::instance()->table();
$SQL .= ' AND ' . $dttable . '.DTT_order = 1 AND ' . $dttable . '.DTT_EVT_end > ' . time() . ' ';
return $SQL;
}
add_filter( 'posts_where', 'featured_event_espresso_listing_where' );
I can see that the join function adds the given SQL code, regardless of using .= or =. I can check this by passing incorrect SQL syntax (using a double SELECT statement for example), so WordPress outputs the full query with a syntax error.
Only using the where part (still) gives me the missing JOIN, which is to be expected.
To be sure, I have events that match the query, so its not failing due to having no matches.
Still I only get a 500 error and no specific error output when the join is used.
I’ve walked through the checks, but none seem to matter to my case. I’ve tried it with $wp_query and the checks, but that did not make a difference.
Can you see the issue? I’m at a loss. To me it seems the query is correct, but the data returned creates an error. When I use the (expected) query directly in the database, there are no issues.
|
|
Josh
|
July 12, 2017 at 12:35 pm
You really do need some conditional checks before filtering the query because the posts_where() and posts_join() hooks are applied to every query WP makes for posts.
It may help if you can post the entire code in a gist or pastebin, it’s not clear from your code examples here at what point in the request that the code is executing.
|
|
Ruben
|
July 16, 2017 at 3:29 pm
Hi Josh,
I’ve placed the featured.php code on pastebin: https://pastebin.com/77Se8tzT
I’ve commented where the EE code starts and stops.
Is it right to assume that the non-existence of checks does not interfere with the query? i.e. I can leave out the checks until the code provides the right posts for the featured slider?
Many thanks again! I’ll continue to try and find out what’s going on, and hope that you can easily shed light on some mistake I’m making.
|
|
Josh
|
July 18, 2017 at 7:23 am
Hi Ruben,
You’ll need to run your code earlier. The trouble you’re having right now is quite likely caused by the time your template gets to your functions that callback on posts_where and posts_join, those two filter hooks have already fired and are done.
Along with that, EE_Event_List_Query is deprecated. I can suggest using the built in models system to do the querying. You can follow the example code from this gist:
https://gist.github.com/joshfeck/6e33532c37a123bbf532
|
