Skip Navigation

[Resolved] views filter on numeric field not working

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

This topic contains 10 replies, has 2 voices.

Last updated by chi-lokL 6 years, 11 months ago.

Assigned support staff: Juan.

Author
Posts
#203916

(The URL provided above is for reference, currently i am working on a local VM)

i have a view and wanna create a filter on two custom fields (created by wp-types).
The fields contain latitude and longitude information for a map to display. Map is working.

However, the filter on numeric simply not work.

My filter is to find, for example, any latitude between 2 values say 22.22 or 23.09, the filter will fail and not return anything at all.
But if i change to integer value, 22 and 23, it will display correctly.

I checked your forum and it appears this is a known bug already.

Please let me know if there is any alternate solution

#204072

Juan
Supporter

Timezone: Europe/Madrid (GMT+01:00)

Hi Chi

This is Juan, lead Views developer. I'm not sure what the problem might be here, but I think we can try to understand it better, and narrow it so we can find a solution.

I understand that it seems that filtering between two numeric with decimal values is not working, but filtering between two integers is working. I do not know that this is a known bug at the moment,but let's find the cause of this.

Please activate the Views debug window in the Views settings screen, and run the two queries: filtering between two integers and between to numbers with decimals. Then, please paste here the SQL query that is being generated. Maybe this is just a wording problem, because "between" means "greater than" plus "lower than", and not "greater and equal than" nor "lower or equal than".

Regards,
Juan de Paco

#204909

Here is the query:

SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) JOIN wp_icl_translations t ON wp_posts.ID = t.element_id
AND t.element_type IN ('post_doctor') JOIN wp_icl_languages l ON t.language_code=l.code AND l.active=1 WHERE 1=1 AND wp_posts.ID NOT IN (630) AND wp_posts.post_type = 'doctor' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND ( (wp_postmeta.meta_key = 'wpcf-latitude' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '22' AND '24') ) AND t.language_code='en' GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

#204910

In addition, I have moved it to the URL below and have the debug mode turned on.
Please help take a look

URL: hidden link

to use, put 22, 24 into latitude min and max respectively

#205129

Juan
Supporter

Timezone: Europe/Madrid (GMT+01:00)

Hi Chi

The query seems right and it should be returning the right values.

I'm enabling the private data box in this ticket, because I can not use the debugger on the URL you suggested unless I am logged in. Please add the credentials to the site, I will take a look at the debug and also to the View generating the page.

Regards,
Juan de Paco

#205906

Here you go

#205907

for between 22 and 24, it will return results
if you enter 22.1 and 24, nothing returns

#206545

Juan
Supporter

Timezone: Europe/Madrid (GMT+01:00)

Hi Chi

I have good news, or at least some good news, about this. I was able to reproduce this issue and found what is causing it. Let me explain it:

By default, when filtering by a DECIMAL value in a custom field, WordPress needs to know the number of total numbers and decimal places that the values are using. If we do not pass that information, it defaults to decimal values with a maximum of 10 digits and 0 decimal values. This means that it does not filter by decimals at all, and also that it rounds the passed values upwards, so filtering by 22.1 <= x <= 23 becomes filtering by 23 <= x <= 23 (and 21.9 <= x <= 23 becomes 22 <= x <= 23).

So the solution is quite simple: pass the amount of decimal places that our values can take, and that is all. In your case, as latitude and longitude can have values of 3 integer digits, and can hold up to 6 decimal places, we need to pass the value (9, 6).

Unfortunately, Views does not have a way to pass that values at the moment,and it is quite difficult to add it to our GUI right now. We will have to find an easy way to implement this, maybe only asking for the number of decimals and implementing the maximum for the overall number of digits, not sure at the moment. But s a workaround our next version is going to implement a filter so you can adjust that values with a little piece of code. In fact, the example that we will show in our documentation will be exactly this one that you need to implement.

I can send you a patch on this to your email, if you want. Once you implement it, you need to add the following code to your functions.php file or to wharever you are adding your custom code:

add_filter('wpv_filter_custom_field_filter_type', 'prefix_my_decimal_filter', 10, 3);

function prefix_my_decimal_filter($type, $meta_name, $view_id) {
if ( $type == 'DECIMAL' && $view_id == 646 ) {
$type = 'DECIMAL(9,6)';
}
return $type;
}

Hope it helps. Let me know if I should pass you the patch or you will wait to our next release.

Regards,
Juan de Paco

#207106

Please pass me the patch and any interactions to use it with

#207123

Juan
Supporter

Timezone: Europe/Madrid (GMT+01:00)

Hi Chi

I just sent you a patched version of the file that you need to upload to your server. You then just need to use the code I gave you above and it should be good to go.

Please check it and let me know how it goes.

Regards,
Juan de Paco

#209813

Finally got time to test this out.

It works