aldiaa

Thank you for your clarification @jeherve, I did not notice that there is a _post_views key in postmeta table. Maybe I have searched by a post ID which its cache data had expired. I think this will solve my issue, especially after extending the cache period as per your advice. Thank you for your swift support.

Update:

I ran a test for 20 actual posts in my websites and found out that my custom query would not resolve the issue as it is very slow (took about 7.5 seconds).

It is even worse using regular expressions (took 18.5 seconds).

The final result would be a list of key-value pairs.

Is there any alternative? Can’t we save the views counter in postmeta table or as a custom field in posts table?

Update: to avoid parsing JSON in PHP, the custom query should be something like this:

SELECT
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            option_value,
            '"post":{"ID":',
            -1
        ),
        ',"post_author"',
        1
    ) AS Post_ID,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(option_value, '"views":', -1),
        ',"years":',
        1
    ) AS Views
FROM
    _DDV_options
WHERE
    option_value LIKE '%"post":{"ID":1234%'
    or option_value LIKE '%"post":{"ID":5678%'

...
    or option_value LIKE '%"post":{"ID":9999%';

Thank you for your informative reply @bcworkz. Unfortunately, I could not find the source data in posts nor postmeta tables. The views counter is part of the long JSON value in options table which is not very easy to manipulate.

Anyway, I think I am in the right direction thanks to your clarifications. I will close this thread as I have opened a new one in the plugin support forum.

Displaying Post Views in Archive Pages | www.remarpro.com

Edit: I managed to write a custom query to get the required data in a good format without the need to parse JSON in PHP.

SELECT
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            option_value,
            '"post":{"ID":',
            -1
        ),
        ',"post_author"',
        1
    ) AS Post_ID,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(option_value, '"views":', -1),
        ',"years":',
        1
    ) AS Views
FROM
    _DDV_options
WHERE
    option_value LIKE '%"post":{"ID":1234%'
    or option_value LIKE '%"post":{"ID":5678%'

...
    or option_value LIKE '%"post":{"ID":9999%';

• This reply was modified 1 year, 9 months ago by aldiaa.
• This reply was modified 1 year, 9 months ago by aldiaa.

Thank you for your reply, @bcworkz.

Actually, I do not trust my current PHP & WP skills, so I do not prefer risky solutions such as editing the default database structure.

Theoretically, solving my problem should be easy by creating a custom query with a join operation which will return a list of posts with their associated views counter before entering the main posts loop. Inside “The Loop”, all the required fields should be displayed per post without any repeated calls to DB.

However, I do not know how to write such a query. It seems that Jetpack statistics are stored with some kind of encrypted names and very long JSON values in _DDV_Options table. If there was a direct “post_id” field in this table, it would be much easier to use as a foreign key.

Any further support would be appreciated.

Thank you @bcworkz, “posts_request” filter was the answer! I just had to fix some syntax issues in my SQL query (missing quotes) and use this magical filter ??

Note: I do not think using SQL wildcards would be a good idea. Each Arabic character has nine forms, so replacing each one by nine characters would result to a long ugly query. For example, “???” is a short word but it will be converted to “[?????????????????][?????????????????][?????????????????]”. This might be easy to write dynamically through PHP, but I am not sure whether the performance would be affected.

Below is my complete code, just for reference.

add_filter("posts_request", "custom_search_filter", 10, 2);

function custom_search_filter($sql, $query) {

if ($query->is_main_query() && is_search()) {
   $sql = get_custom_search_query($query);
}

return $sql;
}

function get_custom_search_query($query){
	$search_term = get_text_clear_of_diacritics($query->get('s'));
	
	$sql = "SELECT * FROM _DDV_posts WHERE 1=1 ";
	$sql .= "AND (((" . get_normalized_field('_DDV_posts.post_title');
	$sql .= " LIKE '%" . $search_term . "%') ";	
	$sql .= "OR (" . get_normalized_field('_DDV_posts.post_content');
	$sql .= " LIKE '%" . $search_term . "%'))) ";
	$sql .= "AND _DDV_posts.post_type = 'post' ";
	$sql .= "AND ((_DDV_posts.post_status = 'publish')) ";
	$sql .= "ORDER BY _DDV_posts.post_date DESC ";
	
	return $sql;
}

function get_text_clear_of_diacritics($original_text){
	return preg_replace("~[\x{064B}-\x{065B}]~u", "", $original_text);
}

function get_normalized_field($column_name){
	return 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
	. $column_name .
		', "D98E", ""), "D98B", ""), "D98F", ""), "D98C",""),"D991",""),"D992",""),"D990",""),"D98D",""),"?","")';
}

• This reply was modified 1 year, 10 months ago by aldiaa.

I am trying now to normalize both sides (MySQL and the search term written in the search textbox) but with no luck. This is my code in functions.php.

add_action('pre_get_posts','searchfilter');

function searchfilter($query) {

if ($query->is_search) {
	get_custom_search_query($query);
}

return $query;
}

function get_custom_search_query($query){
	$search_term = get_text_clear_of_diacritics($query->get('s'));
	
	$sql = "SELECT * FROM _DDV_posts WHERE 1=1 ";
	$sql .= "AND (((" . get_normalized_field(_DDV_posts.post_title);
	$sql .= " LIKE '%" . $search_term . "%') ";	
	$sql .= "OR (" . get_normalized_field(_DDV_posts.post_content);
	$sql .= " LIKE '%" . $search_term . "%'))) ";
	$sql .= "AND _DDV_posts.post_type = 'post' ";
	$sql .= "AND ((_DDV_posts.post_status = 'publish')) ";
	$sql .= "ORDER BY _DDV_posts.post_date DESC ";
	
	$query->get_results($sql);
	//echo  'Last Query: ' . $query->request;
}

function get_text_clear_of_diacritics($original_text){
	return preg_replace("~[\x{064B}-\x{065B}]~u", "", $original_text);
}

function get_normalized_field($column_name){
	return 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(HEX(REPLACE('
	. $column_name .
		', "-", "")), "D98E", ""), "D98B", ""), "D98F", ""), "D98C",""),"D991",""),"D992",""),"D990",""),"D98D","")';
}

The result query is for some reason still showing diacritics.

SELECT SQL_CALC_FOUND_ROWS _DDV_posts.ID
FROM _DDV_posts
WHERE 1=1
AND (((_DDV_posts.post_title LIKE '%??????%')
OR (_DDV_posts.post_excerpt LIKE '%??????%')
OR (_DDV_posts.post_content LIKE '%??????%')))
AND ((_DDV_posts.post_type = 'post'
AND (_DDV_posts.post_status = 'publish'
OR _DDV_posts.post_status = 'private'))
OR (_DDV_posts.post_type = 'page'
AND (_DDV_posts.post_status = 'publish'
OR _DDV_posts.post_status = 'private'))
OR (_DDV_posts.post_type = 'attachment'
AND (_DDV_posts.post_status = 'publish'
OR _DDV_posts.post_status = 'private'))
OR (_DDV_posts.post_type = 'e-landing-page'
AND (_DDV_posts.post_status = 'publish'
OR _DDV_posts.post_status = 'private'))
OR (_DDV_posts.post_type = 'sr_playlist'
AND (_DDV_posts.post_status = 'publish'
OR _DDV_posts.post_status = 'private')))
ORDER BY _DDV_posts.post_title LIKE '%??????%' DESC, _DDV_posts.post_date DESC
LIMIT 0, 20

It seems that my custom query is not being executed at all, it is being overwritten by the default search query. Why?

Edit: if I try to use the global $wpdb, it is NULL.

• This reply was modified 1 year, 10 months ago by aldiaa.
• This reply was modified 1 year, 10 months ago by aldiaa.

Thank you for your reply dear @bcworkz, I thought I can access class fields directly by name without using “get” similar to other languages. Also, I was looking at some WP tutorials and found out the difference between filters and actions.

However, my logic is not correct. What I am trying to do is to make the search insensitive in terms of diacritics. Users usually do not type diacritics when searching but some contents in my site contain these diacritics. So, I need to make MySQL considers “??????”, “??????”, “???” as one word.

I found a good article in this topic, but I am not sure how to access Index.xml file in phpMyAdmin to add the required custom collation. As per the official MySQL documentations, the configuration files should be located in character_sets_dir (/usr/share/percona-server/charsets/), but I do not know how to navigate to these directories.

Arabic Case Insensitive In Database Systems: How To Solve Alef With and Without Hamza Problem | by Ahmed Essam | Medium

MySQL :: MySQL 5.7 Reference Manual :: 10.14.3 Adding a Simple Collation to an 8-Bit Character Set

• This reply was modified 1 year, 10 months ago by aldiaa.