WMPL Pods->field for category causing out of memory

[nobleclem]

Issue Overview

Right now I have a site with ~12k posts, 20 categories, & 5 languages. We encountered a problem where an Editor edited a post associated with 8 categories and of those categories were associated to nearly all of the posts. This edit caused an out of memory issue preventing the post from being edited. After a bunch of digging I came across a query from Pods that generated a result set of 46k rows.

This query was generated from classes/Pods.php field( 'category' ) on Line 1285

SELECT DISTINCT *, 
                `t`.`term_id` AS `pod_item_id` 
FROM   `wp_terms` AS `t` 
       LEFT JOIN `wp_term_taxonomy` AS `tt` 
              ON `tt`.`term_id` = `t`.`term_id` 
       LEFT JOIN `wp_term_relationships` AS `tr` 
              ON `tr`.`term_taxonomy_id` = `tt`.`term_taxonomy_id` 
       LEFT JOIN `wp_icl_translations` AS `wpml_translations` 
              ON `wpml_translations`.`element_id` = `tt`.`term_taxonomy_id` 
                 AND `wpml_translations`.`element_type` = 'tax_category' 
                 AND `wpml_translations`.`language_code` = 'en' 
       LEFT JOIN `wp_icl_languages` AS `wpml_languages` 
              ON `wpml_languages`.`code` = `wpml_translations`.`language_code` 
                 AND `wpml_languages`.`active` = 1 
WHERE  ( ( `t`.`term_id` = 24610 
            OR `t`.`term_id` = 24611 
            OR `t`.`term_id` = 24613 
            OR `t`.`term_id` = 24614 
            OR `t`.`term_id` = 24617 
            OR `t`.`term_id` = 24615 
            OR `t`.`term_id` = 2 
            OR `t`.`term_id` = 24616 ) 
         AND ( `tt`.`taxonomy` = "category" ) 
         AND ( `wpml_languages`.`code` IS NOT NULL ) ) 

That query in addition to its large result set also includes data from all 5 tables which I don't believe is necessary.

Possible Solution

Not knowing exactly what is done with this data afterwords it seems at a minimum the following change should be made: classes/Pods.php field() on Line 1286 update select statement to

'`t`.*, `tt`.*, `tr`.*, `t`.`' . $table[ 'field_id' ] . '` AS `pod_item_id`'

but if only data from the terms table is used then ideally

'`t`.*, `t`.`' . $table[ 'field_id' ] . '` AS `pod_item_id`'

The latter solution is more ideal as instead of in my case 46k rows I will only get 8.

WordPress Environment

``` WordPress Version: 4.9.5 PHP Version: 7.1.17 MySQL Version: 5.5.5 Server Software: Apache/2.2.15 (CentOS) Your User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.139 Safari/537.36 Session Save Path: [REDACTED] Session Save Path Exists: Yes Session Save Path Writeable: Yes Session Max Lifetime: 1440 Opcode Cache: Apc: No Memcached: No OPcache: Yes Redis: No Object Cache: APC: No APCu: No Memcache: No Memcached: No Redis: No WPDB Prefix: wp_ WP Multisite Mode: No WP Memory Limit: 40M Pods Network-Wide Activated: No Pods Install Location: [REDACTED]wp-content/plugins/pods/ Pods Tableless Mode Activated: No Pods Light Mode Activated: No Currently Active Theme: Michigan News 2018 Currently Active Plugins: AJAX Thumbnail Rebuild: 1.2.1 BU Navigation: 1.2.11 MC Google Analytics: 1.1.1 MC Varnish Cache: 1.1.2 MC WP Gallery: 1.0.4 News Custom: 1.0 Page Links To: 2.9.10 Pods - Custom Content Types and Fields: 2.7.1 Posts 2 Posts: 1.6.5 Redirection: 3.2 SSL Insecure Content Fixer: 2.5.0 University of Michigan: MAuth: 1.2 WP Enhancements: 1.5.1 WPML Multilingual CMS: 3.9.4 ```

[nobleclem]

For anyone who has a similar issue and this hasn't been resolved this is my current "fix":

add_filter( 'pods_data_pre_select_params', function( $params ){
    global $wpdb;

    if( ($params['table'] == $wpdb->terms) && isset( $params['join']['wpml_translations'] ) && isset( $params['join']['wpml_languages'] ) ) {
        if( $params['select'] == '*, `t`.`term_id` AS `pod_item_id`' ) {
            $newStars = array(
                '`t`.*'
            );
            foreach( $params['join'] as $key => $val ) {
                if( strpos( $key, 'wpml_' ) === false ) {
                    $newStars[] = "`{$key}`.*";
                }
            }

            $params['select'] = str_replace(
                '*,', implode( ', ', $newStars ) .', ', $params['select']
            );
        }
    }

    return $params;                                                                                                              
});

[jimtrue]

@nobleclem Can you share the adjusted SQL after implementing your filter? Clever solution, btw.

[nobleclem]

sure its pretty simple change though....

SELECT DISTINCT
                '`t`.*, `tt`.*, `tr`.*, `t`.`' . $table[ 'field_id' ] . '` AS `pod_item_id`'
FROM   `wp_terms` AS `t` 
       LEFT JOIN `wp_term_taxonomy` AS `tt` 
              ON `tt`.`term_id` = `t`.`term_id` 
       LEFT JOIN `wp_term_relationships` AS `tr` 
              ON `tr`.`term_taxonomy_id` = `tt`.`term_taxonomy_id` 
       LEFT JOIN `wp_icl_translations` AS `wpml_translations` 
              ON `wpml_translations`.`element_id` = `tt`.`term_taxonomy_id` 
                 AND `wpml_translations`.`element_type` = 'tax_category' 
                 AND `wpml_translations`.`language_code` = 'en' 
       LEFT JOIN `wp_icl_languages` AS `wpml_languages` 
              ON `wpml_languages`.`code` = `wpml_translations`.`language_code` 
                 AND `wpml_languages`.`active` = 1 
WHERE  ( ( `t`.`term_id` = 24610 
            OR `t`.`term_id` = 24611 
            OR `t`.`term_id` = 24613 
            OR `t`.`term_id` = 24614 
            OR `t`.`term_id` = 24617 
            OR `t`.`term_id` = 24615 
            OR `t`.`term_id` = 2 
            OR `t`.`term_id` = 24616 ) 
         AND ( `tt`.`taxonomy` = "category" ) 
         AND ( `wpml_languages`.`code` IS NOT NULL ) ) 

Also I encountered an issue with the filter. Sometimes the value passed is an array and sometimes its an object. So the modified filter is this as I didn't want to change the data type and was in a hurry to fix at the time:

add_filter( 'pods_data_pre_select_params', function( $params ){
    global $wpdb;

    if( is_object( $params ) ) {
        if( ($params->table == $wpdb->terms) && isset( $params->join['wpml_translations'] ) && isset( $params->join['wpml_languages'] ) ) {
            if( $params->select == '*, `t`.`term_id` AS `pod_item_id`' ) {
                $newStars = array(
                    '`t`.*'
                );
                foreach( $params->join as $key => $val ) {
                    if( strpos( $key, 'wpml_' ) === false ) {
                        $newStars[] = "`{$key}`.*";
                    }
                }

                $params->select = str_replace(
                    '*,', implode( ', ', $newStars ) .', ', $params['select']
                );
            }
        }
    }
    else if( is_array( $params ) ) {
        if( ($params['table'] == $wpdb->terms) && isset( $params['join']['wpml_translations'] ) && isset( $params['join']['wpml_languages'] ) ) {
            if( $params['select'] == '*, `t`.`term_id` AS `pod_item_id`' ) {
                $newStars = array(
                    '`t`.*'
                );
                foreach( $params['join'] as $key => $val ) {
                    if( strpos( $key, 'wpml_' ) === false ) {
                        $newStars[] = "`{$key}`.*";
                    }
                }

                $params['select'] = str_replace(
                    '*,', implode( ', ', $newStars ) .', ', $params['select']
                );
            }
        }
    }

    return $params;                                                                                                              
});

I might revisit later and see about reducing the duplicate code just for the data type diff.

[quasel]

@sc0ttkclark anything we can get from that?