help with OR query

Thanks for a very interesting question and for including the source code for the [mla_gallery] shortcode you are trying to use. You have made a creative and plausible attempt to get a reasonable result, but you have run up against the limitations of the WordPress get_posts and WP_Query functions that MLA uses to compose the [mla_gallery] results.

The only real problem with your shortcode parameters is that post_parent is not a taxonomy, it is just a field in the posts database. Taxonomies are more complicated and are implemented with three additional database tables and a lot of code to manage and use them. The tax_query parameter only works for taxonomies.

The taxonomy portion of your query is quite simple, so you don’t need tax_query at all. The closest valid shortcode for your purpose would be:

[mla_gallery numberposts="15" paged=current attachment_category=orchestra post_parent=1971 link="file"  mla_alt_shortcode=gallery type="rectangular"]

This works fine, but as you may have discovered it only includes the items that satisfy both the attachment_category and post_parent parameters, i.e., the “orchestra” items attached to post 1971. The connector is AND, and there is no shortcode syntax for changing the connector to OR as you want to do.

As I stated, its a reasonable request and the SQL query to generate the results you want isn’t complicated. The problem is that the get_posts and WP_Query functions do not provide a parameter for the connector.

You can accomplish your goal if you are willing to use the “MLA Gallery Filters and Actions (Hooks)” to substitute your own SQL query for the one WordPress generates. You can find more information about the Gallery Hooks in the Settings/Media Library Assistant Documentation tab. If this works for you I can give you the PHP code you need to add to the example plugin.

If you want to give this a try I need a bit more information so I can write the code in a way fits your application. The example you give is for attachment_category=orchestra post_parent=1971.

1. Is this the only combination you need?
2. Is attachment_category the only taxonomy you will need?
3. Is post_parent the only other parameter you will need?
4. Are there any other examples you want me to consider?

If you can answer the above questions I can be more helpful. I am confident I can give you a solution. Thanks for your understanding, your patience and your interest in the plugin.

Hi David

Thank you for such a comprehensive reply. I will have a look at using the custom gallery hooks!

The ‘1971’ post is actually just all of rtmedia’s ‘wall post’ uploads. The ideal condition I would like to check for is:

The parent post has a post-type of either - rtmedia_album, topic or reply (rtmedia & bbpress custom post types)

OR

att category = 'orchestra'

Kind Regards
Neil

Thanks for your update with the additional information. I have an idea on how to solve your issue in a way that other MLA users might find useful; I will work out an example and post it here.

I have made some progress on a solution that should work for you. In this post, I am going to solve your first example, “attachment_category=orchestra OR post_parent=1971”, because the approach is the same but the code and SQL is simpler. I will add another post with the “parent is post_type rtmedia_album, topic or reply” when I have worked out the details for that query.

The approach is straightforward. I am using the ‘mla_gallery_query_arguments’ filter to process a custom “taxonomy and parent” parameter, execute an SQL query to get the attachments that meet your criteria and then pass a list of attachment ID values back to [mla_gallery] for the gallery display.

The code is somewhat complicated because your example has a taxonomy parameter and you want to divide the results into pages using < !--nextpage-- >. I have also allowed for multiple post_parent values and constructed the SQL query from an array of clauses so it will be easier to adapt to other applications.

Here is the [mla_gallery] shortcode I used to test my solution:

[mla_gallery my_custom_sql="attachment_category=level-two include_children=true post_parent='1544,1847'" numberposts="3" paged="current"]

You can see I am using a different taxonomy term and I have given two parent values. I have also added include_children=true so attachments assigned to Att. Categories under the level-two term will be added to the results. That may not be needed in your application.

Here is the code you can add to the mla-hooks-example.php example plugin to perform the custom query:

public static function mla_gallery_query_arguments_filter( $all_query_parameters ) {
    //error_log( 'MLAGalleryHooksExample::mla_gallery_query_arguments_filter $all_query_parameters = ' . var_export( $all_query_parameters, true ), 0 );

    self::$all_query_parameters = $all_query_parameters;

    /*
     * This example executes a custom SQL query that cannot be done with the usual WordPress WP_Query
     * arguments. The query results are fed back to the [mla_gallery] shortcode as a list of attachments
     * using the "include" parameter.
     *
     * We use a shortcode parameter of our own to apply this filter on a gallery-by-gallery basis,
     * leaving other [mla_gallery] instances untouched. If the "my_custom_sql" parameter is not present,
     * we have nothing to do. If the parameter IS present, extract taxonomy and parent values,
     * then build a custom query that connects them with "OR" (WordPress would use "AND").
     */
    if ( isset( self::$shortcode_attributes['my_custom_sql'] ) ) {
        /*
         * Make sure $my_query_vars is an array, even if it's empty
         */
        $my_query_vars = self::$shortcode_attributes['my_custom_sql'];
        if ( empty( $my_query_vars ) ) {
            $my_query_vars = array();
        } elseif ( is_string( $my_query_vars ) ) {
            $my_query_vars = shortcode_parse_atts( $my_query_vars );
        }

        if ( isset( $my_query_vars['attachment_category'] ) && isset( $my_query_vars['post_parent'] ) ) {
            global $wpdb;

            // Convert slug to term-taxonomy IDs
            $include_children =  isset( $my_query_vars['include_children'] ) && 'true' == strtolower( trim( $my_query_vars['include_children'] ) );
            $args = array( 'slug' => $my_query_vars['attachment_category'], 'hide_empty' => false );
            $terms = get_terms( 'attachment_category', $args );

            $ttids = array();
            foreach( $terms as $term ) {
                $ttids[] = $term->term_taxonomy_id;

                if ( $include_children ) {
                    $args = array( 'child_of' => $term->term_id, 'hide_empty' => false );
                    $children = get_terms( 'attachment_category', $args );
                    foreach( $children as $child ) {
                        $ttids[] = $child->term_taxonomy_id;
                    }
                } // include_children
            }

            // Allow for multiple parent values
            $post_parents = explode( ',', $my_query_vars['post_parent'] );

            // Build an array of SQL clauses
            $query = array();
            $query_parameters = array();

            $query[] = "SELECT p.ID FROM {$wpdb->posts} AS p LEFT JOIN {$wpdb->term_relationships} as tr";
            $query[] = "ON (p.ID = tr.object_id)";

            $placeholders = array();
            foreach ( $post_parents as $post_parent ) {
                $placeholders[] = '%s';
                $query_parameters[] = $post_parent;
            }
            $query[] = 'WHERE ( p.post_parent IN (' . join( ',', $placeholders ) . ')';

            $placeholders = array();
            foreach ( $ttids as $ttid ) {
                $placeholders[] = '%s';
                $query_parameters[] = $ttid;
            }
            $query[] = 'OR tr.term_taxonomy_id IN (' . join( ',', $placeholders ) . ') )';

            $query[] = "AND (p.post_mime_type LIKE 'image/%%')";
            $query[] = "AND p.post_type = 'attachment'";
            $query[] = "AND p.post_status = 'inherit'";
            $query[] = "GROUP BY p.ID";
            // ORDER BY clause would go here, if needed

            /*
             * Add pagination to our query, then remove it from the query
             * that WordPress will process after we're done.
             */
            $paged = $all_query_parameters['paged'];
            if ( empty( $paged ) ) {
                $paged = 1;
            } elseif ( 'current' == strtolower( $paged ) ) {
                /*
                 * Note: The query variable 'page' holds the pagenumber for a single paginated
                 * Post or Page that includes the <!--nextpage--> Quicktag in the post content.
                 */
                if ( get_query_var( 'page' ) ) {
                    $paged = get_query_var( 'page' );
                } else {
                    $paged = ( get_query_var('paged') ) ? get_query_var('paged') : 1;
                }
            } elseif ( is_numeric( $paged ) ) {
                $paged = absint( $paged );
            } elseif ( '' === $paged ) {
                $paged = 1;
            }

            $limit = absint( ! empty( $all_query_parameters['posts_per_page'] ) ? $all_query_parameters['posts_per_page'] : $all_query_parameters['numberposts'] );
            $offset = $limit * ( $paged - 1);
            if ( 0 < $offset && 0 < $limit ) {
                $query[] = 'LIMIT %d, %d';
                $query_parameters[] = $offset;
                $query_parameters[] = $limit;
            } elseif ( 0 < $limit ) {
                $query[] = 'LIMIT %d';
                $query_parameters[] = $limit;
            } elseif ( 0 < $offset ) {
                $query[] = 'LIMIT %d, %d';
                $query_parameters[] = $offset;
                $query_parameters[] = 0x7FFFFFFF; // big number!
            }

            $all_query_parameters['paged'] = NULL;
            $all_query_parameters['posts_per_page'] = 0;
            $all_query_parameters['numberposts'] = 0;

            $query =  join(' ', $query);
            $ids = $wpdb->get_results( $wpdb->prepare( $query, $query_parameters ) );
            if ( is_array( $ids ) ) {
                $includes = array();
                foreach ( $ids as $id ) {
                    $includes[] = $id->ID;
                }
                $all_query_parameters['include'] = implode( ',', $includes );
            } else {
                $all_query_parameters['include'] = '1'; // return no images
            }
        } else {
            $all_query_parameters['include'] = '1'; // return no images
        }
    } // parameter "my_custom_sql" is present

    return $all_query_parameters;
} // mla_gallery_query_arguments_filter

Have a look at the above code. If you can, try adding it to your application and get it working there. If you have any problems or questions, post them here.

I’ll start working on the final part of your rtMedia query. Thanks for a great question and for your patience.

The “parent post type” modification turned out to be a minor variation on the initial solution posted above. Here is the new [mla_gallery] shortcode I used to test the new version:

[mla_gallery my_custom_sql="attachment_category=level-two include_children=true parent_type='post,page'" columns="5" size=icon]

The new parent_type='post,page' parameter has been substituted for the earlier post_parent parameter. I put the new code between the earlier code and the last } else { statement at the bottom of the function. For brevity I have included only the new code and the last few lines of the function here:

} elseif ( isset( $my_query_vars['attachment_category'] ) && isset( $my_query_vars['parent_type'] ) ) {
            global $wpdb;

            // Convert slug to term-taxonomy IDs
            $include_children =  isset( $my_query_vars['include_children'] ) && 'true' == strtolower( trim( $my_query_vars['include_children'] ) );
            $args = array( 'slug' => $my_query_vars['attachment_category'], 'hide_empty' => false );
            $terms = get_terms( 'attachment_category', $args );

            $ttids = array();
            foreach( $terms as $term ) {
                $ttids[] = $term->term_taxonomy_id;

                if ( $include_children ) {
                    $args = array( 'child_of' => $term->term_id, 'hide_empty' => false );
                    $children = get_terms( 'attachment_category', $args );
                    foreach( $children as $child ) {
                        $ttids[] = $child->term_taxonomy_id;
                    }
                } // include_children
            }

            // Allow for multiple parent post_type values
            $parent_types = explode( ',', $my_query_vars['parent_type'] );

            // Build an array of SQL clauses
            $query = array();
            $query_parameters = array();

            $query[] = "SELECT p.ID FROM {$wpdb->posts} AS p";
            $query[] = "LEFT JOIN {$wpdb->posts} as p2";
            $query[] = "ON (p.post_parent = p2.ID)";
            $query[] = "LEFT JOIN {$wpdb->term_relationships} as tr";
            $query[] = "ON (p.ID = tr.object_id)";

            $placeholders = array();
            foreach ( $parent_types as $parent_type ) {
                $placeholders[] = '%s';
                $query_parameters[] = $parent_type;
            }
            $query[] = 'WHERE ( p2.post_type IN (' . join( ',', $placeholders ) . ')';

            if ( count( $ttids ) ) {
                $placeholders = array();
                foreach ( $ttids as $ttid ) {
                    $placeholders[] = '%s';
                    $query_parameters[] = $ttid;
                }
                $query[] = 'OR tr.term_taxonomy_id IN (' . join( ',', $placeholders ) . ') )';
            } else {
                $query[] = ')';
            }

            $query[] = "AND (p.post_mime_type LIKE 'image/%%')";
            $query[] = "AND p.post_type = 'attachment'";
            $query[] = "AND p.post_status = 'inherit'";
            $query[] = "GROUP BY p.ID";
            // ORDER BY clause would go here, if needed

            /*
             * Add pagination to our query, then remove it from the query
             * that WordPress will process after we're done.
             */
            $paged = $all_query_parameters['paged'];
            if ( empty( $paged ) ) {
                $paged = 1;
            } elseif ( 'current' == strtolower( $paged ) ) {
                /*
                 * Note: The query variable 'page' holds the pagenumber for a single paginated
                 * Post or Page that includes the <!--nextpage--> Quicktag in the post content.
                 */
                if ( get_query_var( 'page' ) ) {
                    $paged = get_query_var( 'page' );
                } else {
                    $paged = ( get_query_var('paged') ) ? get_query_var('paged') : 1;
                }
            } elseif ( is_numeric( $paged ) ) {
                $paged = absint( $paged );
            } elseif ( '' === $paged ) {
                $paged = 1;
            }

            $limit = absint( ! empty( $all_query_parameters['posts_per_page'] ) ? $all_query_parameters['posts_per_page'] : $all_query_parameters['numberposts'] );
            $offset = $limit * ( $paged - 1);
            if ( 0 < $offset && 0 < $limit ) {
                $query[] = 'LIMIT %d, %d';
                $query_parameters[] = $offset;
                $query_parameters[] = $limit;
            } elseif ( 0 < $limit ) {
                $query[] = 'LIMIT %d';
                $query_parameters[] = $limit;
            } elseif ( 0 < $offset ) {
                $query[] = 'LIMIT %d, %d';
                $query_parameters[] = $offset;
                $query_parameters[] = 0x7FFFFFFF; // big number!
            }

            $all_query_parameters['paged'] = NULL;
            $all_query_parameters['posts_per_page'] = 0;
            $all_query_parameters['numberposts'] = 0;

            $query =  join(' ', $query);
            $ids = $wpdb->get_results( $wpdb->prepare( $query, $query_parameters ) );
            if ( is_array( $ids ) ) {
                $includes = array();
                foreach ( $ids as $id ) {
                    $includes[] = $id->ID;
                }
                $all_query_parameters['include'] = implode( ',', $includes );
            } else {
                $all_query_parameters['include'] = '1'; // return no images
            }
        } else {
            $all_query_parameters['include'] = '1'; // return no images
        }
    } // parameter "my_custom_sql" is present

    return $all_query_parameters;
} // mla_gallery_query_arguments_filter

This new code also checks the case where no term_taxonomy_ids match the slug provided in the shortcode. Look for a bit of new code starting with if ( count( $ttids ) ) {. This code should also be added to the earlier example.

I hope that gets you started on building custom SQL queries to handle requirements that cannot be met with the WordPress get_posts and WP_Query functions that MLA uses to compose the [mla_gallery] results. I will be adding the above code to the example plugin in my next MLA version.

I am marking this issue resolved, but please update it if you have any problems with or further questions about the solution(s) I have outlined. Thanks for stimulating a great example of using the MLA Gallery Hooks to good effect!

I have re-written the filter code required to support all of the queries discussed above. The new code is smaller and more flexible:

• It allows any taxonomy to be specified, not just “attachment_category”
• It allows for multiple taxonomy terms
• It allows any combination of the three query arguments; taxonomy term(s), post_parent(s) and/or parent_type(s)

Here is the revised code:

public static function mla_gallery_query_arguments_filter( $all_query_parameters ) {
    //error_log( 'MLAGalleryHooksExample::mla_gallery_query_arguments_filter $all_query_parameters = ' . var_export( $all_query_parameters, true ), 0 );

    self::$all_query_parameters = $all_query_parameters;

    /*
     * This example executes a custom SQL query that cannot be done with the usual WordPress WP_Query
     * arguments. The query results are fed back to the [mla_gallery] shortcode as a list of attachments
     * using the "include" parameter.
     *
     * We use a shortcode parameter of our own to apply this filter on a gallery-by-gallery basis,
     * leaving other [mla_gallery] instances untouched. If the "my_custom_sql" parameter is not present,
     * we have nothing to do. If the parameter IS present, extract taxonomy and parent values,
     * then build a custom query that connects them with "OR" (WordPress would use "AND").
     */
    if ( isset( self::$shortcode_attributes['my_custom_sql'] ) ) {
        global $wpdb;

        // Make sure $my_query_vars is an array, even if it's empty
        $my_query_vars = self::$shortcode_attributes['my_custom_sql'];
        if ( empty( $my_query_vars ) ) {
            $my_query_vars = array();
        } elseif ( is_string( $my_query_vars ) ) {
            $my_query_vars = shortcode_parse_atts( $my_query_vars );
        }

        // Start with empty parameter values
        $ttids = array();
        $post_parents = array();
        $parent_types = array();

        // Find taxonomy argument, if present, and collect terms
        $taxonomies = get_taxonomies( array( 'object_type' => array( 'attachment' ) ), 'names' );
        foreach( $taxonomies as $taxonomy ) {
            if ( empty( $my_query_vars[ $taxonomy ] ) ) {
                continue;
            }

            // Found the taxonomy; collect the terms
            $include_children =  isset( $my_query_vars['include_children'] ) && 'true' == strtolower( trim( $my_query_vars['include_children'] ) );

            // Allow for multiple term slug values
            $terms = array();
            $slugs = explode( ',', $my_query_vars[ $taxonomy ] );
            foreach ( $slugs as $slug ) {
                $args = array( 'slug' => $slug, 'hide_empty' => false );
                $terms = array_merge( $terms, get_terms( $taxonomy, $args ) );
            }

            foreach( $terms as $term ) {
                // Index by ttid to remove duplicates
                $ttids[ $term->term_taxonomy_id ] = $term->term_taxonomy_id;

                if ( $include_children ) {
                    $args = array( 'child_of' => $term->term_id, 'hide_empty' => false );
                    $children = get_terms( 'attachment_category', $args );
                    foreach( $children as $child ) {
                        $ttids[] = $child->term_taxonomy_id;
                    }
                } // include_children
            } // $term

            break;
        }

        if ( isset( $my_query_vars['post_parent'] ) ) {
            // Allow for multiple parent values
            $post_parents = explode( ',', $my_query_vars['post_parent'] );
        }

        if ( isset( $my_query_vars['parent_type'] ) ) {
            // Allow for multiple parent values
            $parent_types = explode( ',', $my_query_vars['parent_type'] );
        }

        // Build an array of SQL clauses
        $query = array();
        $query_parameters = array();

        $query[] = "SELECT p.ID FROM {$wpdb->posts} AS p";

        if ( ! empty( $parent_types ) ) {
            $query[] = "LEFT JOIN {$wpdb->posts} as p2";
            $query[] = "ON (p.post_parent = p2.ID)";
        }

        if ( ! empty( $ttids ) ) {
            $query[] = "LEFT JOIN {$wpdb->term_relationships} as tr";
            $query[] = "ON (p.ID = tr.object_id)";
        }

        // Start with a WHERE clause that doesn't match anything, since OR is the connector
        $query[] = 'WHERE ( ( 1=0 )';

        if ( ! empty( $post_parents ) ) {
            $placeholders = array();
            foreach ( $post_parents as $post_parent ) {
                $placeholders[] = '%s';
                $query_parameters[] = $post_parent;
            }

            $query[] = 'OR ( p.post_parent IN (' . join( ',', $placeholders ) . ') )';
        }

        if ( ! empty( $parent_types ) ) {
            $placeholders = array();
            foreach ( $parent_types as $parent_type ) {
                $placeholders[] = '%s';
                $query_parameters[] = $parent_type;
            }

            $query[] = 'OR ( p2.post_type IN (' . join( ',', $placeholders ) . ') )';
        }

        if ( ! empty( $ttids ) ) {
            $placeholders = array();
            foreach ( $ttids as $ttid ) {
                $placeholders[] = '%s';
                $query_parameters[] = $ttid;
            }

            $query[] = 'OR ( tr.term_taxonomy_id IN (' . join( ',', $placeholders ) . ') )';
        }

        // Close the WHERE clause
        $query[] = ')';

        $query[] = "AND (p.post_mime_type LIKE 'image/%%')";
        $query[] = "AND p.post_type = 'attachment'";
        $query[] = "AND p.post_status = 'inherit'";
        $query[] = "GROUP BY p.ID";
        // ORDER BY clause would go here, if needed

        /*
         * Add pagination to our query, then remove it from the query
         * that WordPress will process after we're done.
         */
        $paged = $all_query_parameters['paged'];
        if ( empty( $paged ) ) {
            $paged = 1;
        } elseif ( 'current' == strtolower( $paged ) ) {
            /*
             * Note: The query variable 'page' holds the pagenumber for a single paginated
             * Post or Page that includes the <!--nextpage--> Quicktag in the post content.
             */
            if ( get_query_var( 'page' ) ) {
                $paged = get_query_var( 'page' );
            } else {
                $paged = ( get_query_var('paged') ) ? get_query_var('paged') : 1;
            }
        } elseif ( is_numeric( $paged ) ) {
            $paged = absint( $paged );
        } elseif ( '' === $paged ) {
            $paged = 1;
        }

        $limit = absint( ! empty( $all_query_parameters['posts_per_page'] ) ? $all_query_parameters['posts_per_page'] : $all_query_parameters['numberposts'] );
        $offset = $limit * ( $paged - 1);
        if ( 0 < $offset && 0 < $limit ) {
            $query[] = 'LIMIT %d, %d';
            $query_parameters[] = $offset;
            $query_parameters[] = $limit;
        } elseif ( 0 < $limit ) {
            $query[] = 'LIMIT %d';
            $query_parameters[] = $limit;
        } elseif ( 0 < $offset ) {
            $query[] = 'LIMIT %d, %d';
            $query_parameters[] = $offset;
            $query_parameters[] = 0x7FFFFFFF; // big number!
        }

        $all_query_parameters['paged'] = NULL;
        $all_query_parameters['posts_per_page'] = 0;
        $all_query_parameters['numberposts'] = 0;

        $query =  join(' ', $query);
        $ids = $wpdb->get_results( $wpdb->prepare( $query, $query_parameters ) );
        if ( is_array( $ids ) ) {
            $includes = array();
            foreach ( $ids as $id ) {
                $includes[] = $id->ID;
            }
            $all_query_parameters['include'] = implode( ',', $includes );
        } else {
            $all_query_parameters['include'] = '1'; // return no images
        }
    } // parameter "my_custom_sql" is present

    return $all_query_parameters;
} // mla_gallery_query_arguments_filter

I have incorporated the code in the mla-hooks-example.php.txt example plugin for my next MLA version, which is due out shortly.

Please let me know if you have any trouble adding this code to your site. Thanks again for your question and your interest in the plugin.

Wow David!

Thank you for all your effort! I will implement your suggestions this week..

Best Regards
Neil

Neil,

Thanks for the update. I have released MLA v1.90, which contains the above code in the mla-hooks-example.php.txt example plugin, so it should be easy for you do experiment with it.

Let me know how it goes.