vazco_cache_metadata - search and filtering optimizer v1.0

Release Notes

First release

  • Hello Vasco, just to tell you that the core change in elgg 1.8.8 isn't possible since the function changed..

     

    I added some custom fields too that I can't find with the elgg default search, this pluggin seemed to be the graal but I'm disapointed to see it not working..

     

    Hope you will port it asap for elgg 1.8.8

     

     

  • Installation instructions fore Elgg 1.8.x are:

    In file /engine/lib/metastrings.php Find code in function elgg_entities_get_metastrings_options:

    	$clauses = elgg_get_entity_metadata_where_sql('e', $n_table, $options["{$type}_names"],
    		$options["{$type}_values"], $options["{$type}_name_value_pairs"],
    		$options["{$type}_name_value_pairs_operator"], $options["{$type}_case_sensitive"],
    		$options["order_by_{$type}"], $options["{$type}_owner_guids"]);
    

    Replace with:

    	if(function_exists('vazco_cache_metadata_where_sql')) {
    		$clauses = vazco_cache_metadata_where_sql($options, $type, 'e', $n_table);
    	} else {
    		$clauses = elgg_get_entity_metadata_where_sql('e', $n_table, $options["{$type}_names"],
    			$options["{$type}_values"], $options["{$type}_name_value_pairs"],
    			$options["{$type}_name_value_pairs_operator"], $options["{$type}_case_sensitive"],
    			$options["order_by_{$type}"], $options["{$type}_owner_guids"]);
    	}
    
  • To refresh the cache tables (create new ones, refill after scheme changes) after changing configuration (vazco_cache_metadata::getOptions method), you have to log in as administrator and add ?cache_revalidate=1 to any page. You should to this only when you change configuration of the plugin. It has to be done manually for the performance reasons. With some small performance penalty, you can put vazco_cache_metadata::cacheRevalidate() directly in vazco_cache_metadata_plugins_boot function - it makes actual changes only when there's something to change.

  • seems cool, can we add more than one instance to $cache_metadata_options?

    If I want to cache search of users and maybe groups?

    Thanks.

  • Elgg 1.8.15

    In file /engine/lib/metastrings.php I can not find function elgg_entities_get_metastrings_options

     

  • how to formulate a search query?

    search?tag=tag1+tag2

    finds nothing

  • iluxin, this plugin don't interfere with URL's of search queries in any way, standard query should work

  • how to change the encoding of a table created by this plugin?

  • add ?cache_revalidate=1 and again, all non-Latin characters are "???"

  • is this working in elgg 1.8.16??

    if not can tell me how to setup it

     

  • It should work just fine. The installation instruction numbers of lines may be littile off, but I don't recall any changes in the place you need to put a corechange.

    See: Corechanges.txt file for installation instruction.

    Note that this plugin optimizes backend only. It doesn't affect any existing functionality, but optimizes very specific kind of queries performed by the core.

  • ok but i am getting error

     

    This plugin is invalid: Missing required 'requires' attribute in manifest for plugin vazco_cache_metadata.

  • i changed the manifest file then its working

  • Hi Paweł, I'm kinda strugeling with the thing that I don't really know if it is installed correctly or not.

    I made a custom search page where I'm searching a lot of metadata and I notice my CPU usage (of MySQL is getting way to high using my plugin) and I'd like to cache the metadata to make this better.

    I think I installed everything correctly but there are no changes in my CPU usage. Can I see somewhere if the plugin is doing it's work?

  • allright, I was stupod, I found the database field and I see the fields are written in there, sorry :) It works!

  • Hi,

    i have a problem when performing the search. The new table with all the metadata was created successful.

    Does it use this table when i make elgg_list_entities_from_metadata($options);

    Because when i switch on trace-level NOTICE the SQL-queries seem to be the same and not using the new table.

    Maybe you have an example of usage?

     

  • Stoffal, did you apply corechanges from the plugin? If so, what version of Elgg are you using? (this plugin was tested on Elgg 1.8, not 1.9 yet)

    elgg_list_entities_from_metadata should work without problem. This plugin is completely transparent after doing corechanges and building new tables.

  • I am using elgg 1.8.

    everything is working perfect, but my question is, if i have to build my own sql query to use this new table ?

  • No, after you do corechanges, queries will be automatically built for you.

  • ok, i have a problem and get FATAL ERROR MySQL server has gone away, i have 29 users in the database so something is wrong...

    can i show you my code?

    <?php
    /**
     * Show search result
     *
     */

    $looking_for = get_input('looking_for');
    $i_am = get_input('i_am');
    $age_from = get_input('age_from');
    $age_to = get_input('age_to');
    $location = get_input('location');
    $radius = get_input('radius');


    $name_value_pairs = array();

    // lookin_for and i_am must be vice-versa
    if ( $looking_for  ){
        $name_value_pairs['gender'] = array(
                'name'  => 'gender',
                'value' => $looking_for,
        );
    }

    if ( $i_am  ){
        $name_value_pairs['looking_for_gender'] = array(
                'name'  => 'looking_for_gender',
                'value' => $i_am,
        );
    }

    $now = time();

    if ( $age_from  ){
        $name_value_pairs['age_from'] = array(
                'name'  => 'birthday',
                'value' => ($now - $age_from*60*60*24*365.25),
                'operand' => '<='
        );
    }

    if ( $age_to  ){
        $name_value_pairs['age_to'] = array(
                'name'  => 'birthday',
                'value' => ($now - ($age_to+1)*60*60*24*365.25),
                'operand' => '>='
        );
    }

    if ( $location  ){

        $coordinates = getLongLat($location);
        
        /*
        $name_value_pairs['location'] = array(
                'name'  => 'location',
                'value' => '%'.$location.'%',
                'operand' => 'LIKE'
        );
    */
        
        $geoLocation = getLongLat($location);
        
        $latitude = $geoLocation->lat;
        $longitude =  $geoLocation->lng;
        
        if ( $radius && $latitude && $longitude){
            
            $lat_range = $radius/69.172;
            $lon_range = abs($radius/(cos($latitude) * 69.172));
            
            $min_lat = number_format($latitude - $lat_range, "4", ".", "");
            $max_lat = number_format($latitude + $lat_range, "4", ".", "");
            $min_lon = number_format($longitude - $lon_range, "4", ".", "");
            $max_lon = number_format($longitude + $lon_range, "4", ".", "");
            
            $name_value_pairs['min_lat'] = array(
                    'name'  => 'lat',
                    'value' => $min_lat,
                    'operand' => '>='
            );
            
            $name_value_pairs['max_lat'] = array(
                    'name'  => 'lat',
                    'value' => $max_lat,
                    'operand' => '<='
            );
            
            
            $name_value_pairs['min_long'] = array(
                    'name'  => 'lng',
                    'value' => $min_lon,
                    'operand' => '>='
            );
            
            $name_value_pairs['max_long'] = array(
                    'name'  => 'lng',
                    'value' => $max_lon,
                    'operand' => '<='
            );
            
        }
    }

    $options['metadata_name_value_pairs'] = $name_value_pairs;
    $options['metadata_name_value_pairs_operator'] = 'AND';

    $content = elgg_list_entities_from_metadata($options);

    FOLLOWING ERROR:

    MySQL server has gone away

    QUERY: SELECT count(DISTINCT e.guid) as total FROM elgg_entities e JOIN elgg_metadata n_table on
    e.guid = n_table.entity_guid JOIN elgg_metadata n_table1
    on e.guid = n_table1.entity_guid JOIN elgg_metastrings msn1
    on n_table1.name_id = msn1.id JOIN elgg_metastrings msv1
    on n_table1.value_id = msv1.id JOIN elgg_metadata n_table2
    on e.guid = n_table2.entity_guid JOIN elgg_metastrings msn2
    on n_table2.name_id = msn2.id JOIN elgg_metastrings msv2
    on n_table2.value_id = msv2.id JOIN elgg_metadata n_table3
    on e.guid = n_table3.entity_guid JOIN elgg_metastrings msn3
    on n_table3.name_id = msn3.id JOIN elgg_metastrings msv3
    on n_table3.value_id = msv3.id JOIN elgg_metadata n_table4
    on e.guid = n_table4.entity_guid JOIN elgg_metastrings msn4
    on n_table4.name_id = msn4.id JOIN elgg_metastrings msv4
    on n_table4.value_id = msv4.id JOIN elgg_metadata n_table5
    on e.guid = n_table5.entity_guid JOIN elgg_metastrings msn5
    on n_table5.name_id = msn5.id JOIN elgg_metastrings msv5
    on n_table5.value_id = msv5.id JOIN elgg_metadata n_table6
    on e.guid = n_table6.entity_guid JOIN elgg_metastrings msn6
    on n_table6.name_id = msn6.id JOIN elgg_metastrings msv6
    on n_table6.value_id = msv6.id JOIN elgg_metadata n_table7
    on e.guid = n_table7.entity_guid JOIN elgg_metastrings msn7
    on n_table7.name_id = msn7.id JOIN elgg_metastrings msv7
    on n_table7.value_id = msv7.id JOIN elgg_metadata n_table8
    on e.guid = n_table8.entity_guid JOIN elgg_metastrings msn8
    on n_table8.name_id = msn8.id JOIN elgg_metastrings msv8
    on n_table8.value_id = msv8.id WHERE (((msn1.string = 'gender' AND BINARY msv1.string
    = 'lf_search_simple:input:female' AND ( (1 = 1) and n_table1.enabled='yes')) AND (msn2.string = 'looking_for_gender' AND BINARY msv2.string
    = 'lf_search_simple:input:females' AND ( (1 = 1) and n_table2.enabled='yes')) AND (msn3.string = 'birthday' AND BINARY msv3.string
    <= 695972924 AND ( (1 = 1) and n_table3.enabled='yes')) AND (msn4.string = 'birthday' AND BINARY msv4.string
    >= 475069724 AND ( (1 = 1) and n_table4.enabled='yes')) AND (msn5.string = 'lat' AND BINARY msv5.string
    >= 48.0636 AND ( (1 = 1) and n_table5.enabled='yes')) AND (msn6.string = 'lat' AND BINARY msv6.string
    <= 48.3527 AND ( (1 = 1) and n_table6.enabled='yes')) AND (msn7.string = 'lng' AND BINARY msv7.string
    >= 16.0646 AND ( (1 = 1) and n_table7.enabled='yes')) AND (msn8.string = 'lng' AND BINARY msv8.string
    <= 16.6830 AND ( (1 = 1) and n_table8.enabled='yes')))) AND (e.site_guid IN (1)) AND ( (1 = 1) and e.enabled='yes')

     

    Any idea would be very helpful!

  • Hello my friend. I need to install vazco_cache_metadata - search and filtering optimizer for Elgg 1.8.16 but does not work when I change metadata.php
    You could tell me as I do please.
    Thank you very much.
    Angel

Mike Zacher (vazco)

I'm an Elgg expert and CEO of vazco.eu - the first plugin store for Elgg. We focus on development of highly scalable and highly customized Elgg websites.

Stats

  • Category: Tools
  • License: GNU General Public License (GPL) version 2
  • Updated: 2014-11-17
  • Downloads: 956
  • Recommendations: 5

Other Projects

View Mike Zacher (vazco)'s plugins