[Resolved] Views MySQL query not including distance and filter parameters

[Author]

Posts

[darryl]

I am trying to: display records on a google map that are within 2 miles of the search location

Link to a page where the issue can be seen:
hidden link

I expected to see:
something like this: hidden link

A map with pins in it

Instead, I got:
No results found

---

using the debugger, the mysql query is this:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND (
wp_postmeta.meta_key = 'rehub_views'
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC, wp_posts.post_date DESC

Which when ran in mysql actually returns 4k+ records. The debugger shows 0 items found though. The mysql query is also missing the distance and filter parameters.

[Christian Cox]

Hi, it looks like none of the other custom field filters are applied to the query either, so that seems to indicate a conflict with another plugin or possibly the theme. Please try these troubleshooting steps first:
- Temporarily activate a default theme like Twenty Seventeen, then deactivate all plugins except Types and Views.
- Test the search form again. If the results are how filtered as expected, reactivate your theme and other plugins one by one until the conflict is revealed.
- If the results were not filtered as expected, it could indicate a problem with the Query Filter, so you could try deleting the Query Filters, then deleting all the Filter Controls from the Filter Editor panel. This will clean up the View query and you can rebuild the filters. Begin re-adding filters in the Filter Editor panel. If you want to apply any static filters after that, use the Query Filter panel.

[darryl]

Hi,

I found what could be an issue. In the view if i set a limit to 50 the query, the page will display results. You can see them here.
hidden link

The problem though is that the distance filter will only apply the the most recent 50 posts and so obviously that's not going to return the right records.

It looks like what's happening is that the distance filter is applied after the records are returned (in this case the entire record set ~4400) and so after a certain size that results in a calculation that times out hence no records.

that why this function worked when the record set was smaller, but is now failing after a more recent import. Does this sound possible, if so whats the largest functional limit for this to work? Also is there a way so that the distance filter be applied as a query parameter to limit the response calculated against instead of filtering the response afterwards? It looks like that's the issue.

[Shane]

Hi Darryl,

As Christian i currently on vacation, I will be handling this ticket.

It seems that this might be a limitation on the Google API as well. Could you let me know how many posts are you currently have ?

Thanks,
Shane

[darryl]

Hi Shane,

Thank you for the followup. It shouldn't be an issue with the Google API as it works when i set the limit on the search and doesn't work when I release the limit. If it was an API issue, it would totally fail every time. Indeed if you look at the debug payload, you can see the results being returned, it's just the filters aren't being applied.

The other way it works is if I release the limit, but set a different static query filter that will limit the results to in this case 1602.

I've attached three screenshots 2 work and 1 that does not. Is there a way to set up a hook that will dynamically set up a filter that will be applied to the query before the distance calculation is applied? It seems like the distance calculation is being overwhelmed by the database size and causing the remaining filters to fail somehow.

The total record set size is currently 4,943. I still have about 2k more records to import.

Thanks,

[Shane]

Hi Darryl,

Would you mind providing me with a duplicator package for the site so I can test locally.

It seems that its hitting some query limits with either the Google API or with your MYSQL.
https://toolset.com/faq/provide-supporters-copy-site/
I can test this locally and see what would happen.

Thanks,
Shane

[Shane]

Hi Darryl,

Thank you for the duplicator package but it seems that its just the database so i'm not able to get an exact setup.

Could you install the all in one backup plugin and create the backup with that.

Also could you check the php logs and mysql logs to see if there is any errors being thrown there.

Thanks,
Shane

[darryl]

Hi Shane,

Here's the link to download the all in one backup
hidden link

Here's a link to the php error log
hidden link

Looking at the errors, it looks like the query is failing because our host doesn't allow for queries that are longer than 16k characters. looks like we'll need a way to segment the record set before the map distance calculation retrieves the entire dataset.

With the address, we can identify the county which should put us back under the limit and it should work again if we can inject that parameter into the query. Let me know if this is possible or something else. Thanks,

[Shane]

Hi Darryl,

Thank you for the explanation.

Unfortunately i'm not able to give any information on how to segment the queries. However not returning all the information at once would certainly help as you mentioned with the limiting the view to 50 .

Could you try using pagination ?

Add the pagination so all the information is not returned at once.

Please le tme know the results.

Thanks,
Shane

[darryl]

Hi Shane,

I did try adding pagination and the query fails.

I did find this link:
https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query

would you be able to help me find how to use the Maps plugin to get the county or city from and address in the functions.php? If you can help me with that, I should be able to take it the rest of the way using the filter.

thanks,

[darryl]

Hi Shane,

I was able to get the wpv_filter_query filter into functions.php. I've hardcoded parameters to shrink the record set to something manageable. In the debugger, it's showing the filters, but it doesn't update the mysql query so it's still failing because the query is too long in length. shouldn't the filters modify the mysql query? Not sure if there's something not working here.

Here's the code in functions.php

add_filter( 'wpv_filter_query', 'pp_query_county_filter', 1, 3  );
function pp_query_county_filter( $query_args,  $view_settings, $view_id ) {
    

	$viewArray = [611];
    if( in_array($view_id, $viewArray) ) {
	    $query_args['medafi_county'] = 'Orange';
	    $query_args['medafi_city'] = 'Newport Beach';


	}

    return $query_args;
}

the url is still the same
hidden link

[Shane]

Hi Darryl,

Sorry for the delay in response as the forums have been quite busy.

The filter should actually limit the the query for sql.

What you can do is to check the query that is being sent by going to Toolset -> Settings -> Frontend Content and enabling the views debugging so the popup will show.

It shows the sql query that is being generated

Also the priority should be 99 and not one. Could you try setting this to 99 and let me know the results as well as if the sql query has been changed.

Thanks,
Shane

[darryl]

Thanks shane, i was able to get the types views query filtering to work, my issue was though that if an address was on the boarder of a city, county or zipcode, locations still within range but on the other side of the city line wouldn't show up.

I was able to resolve the issue by adding a filter to return the address zipcode and then do a lookup to get the neighboring zipcodes and then use zipcode list as my query parameters to limit the scope of the mysql search.

Now when an address is submitted, I'll get all locations within the search radius of the location. Thanks for your help shane.