Use _wpsso_meta-> schema_review_rating in meta_query

  • Resolved commadelimited

    (@commadelimited)


    5 months, 2 weeks ago

    I’m working on a plugin which will retrieve posts on my site by rating (currently stored in _wpsso_meta-> schema_review_rating). Because the schema_review_rating value is nested within the _wpsso_meta meta value, I’m not sure how to compare against the desired value. For example, our ratings are in .5 increments on a .5 to 5 scale. If the property wasn’t nested, I believe the following query would do the trick.

    $reviews = get_posts([
      'post_type' => 'reviews',
      'post_status' => 'publish',
      'posts_per_page' => 10,
      'order' => 'ASC',
      'orderby' => 'title',
      'meta_query' => array(
        'relation' => 'OR',
          array(
            'key' => 'schema_review_rating',
            'value' => $rating,
            'compare' => '='
          ),
          array(
            'key' => 'schema_review_rating',
            'value' => $rating . '.5',
            'compare' => '='
          )
        )
    ]);

    The goal of the previous query is to retrieve all posts with a rating of 4 or 4.5, and display them ordered by post title. Any suggestions on how to proceed?

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter commadelimited

    (@commadelimited)

    Oh, I think I figured it more quickly than expected. On initial review it seems like this query will do the trick. Note the LIKE comparison into the serialized array. We’re storing either 4 or 4.5, which results in a string of either 1 or 2 characters.

    $reviews = get_posts(
        [
            'post_type' => 'reviews',
            'post_status' => 'publish',
            'posts_per_page' => 10,
            'order' => 'ASC',
            'orderby' => 'title',
            'meta_query' => array(
                'relation' => 'OR',
                array(
                    'key' => '_wpsso_meta',
                    'value' => '"schema_review_rating";s:1:"' . $rating . '";',
                    'compare' => 'LIKE'
                ),
                array(
                    'key' => '_wpsso_meta',
                    'value' => '"schema_review_rating";s:2:"' . $rating . '.5";',
                    'compare' => 'LIKE'
                )
            )
        ]
    );
    Plugin Author JS Morisset

    (@jsmoriss)

    Storing each metadata key individually in the database has an impact on performance, so good plugins and themes store their settings as a single array instead. Unfortunately the WordPress ‘meta_query’ doesn’t handle array element matching since the ‘meta_query’ value creates an SQL query, and arrays are stored serialized in the database, so using LIKE as you’ve done is pretty much the only way to match a string within the serialized array.

    WPSSO creates several ‘_wpsso_head_info_*’ entries in the post/term/user/comment metadata tables, which are created dynamically based on the meta tags and Schema markup. I’ve added a new ‘_wpsso_head_info_schema_review_rating’ metadata value in WPSSO Core v18.14.0-dev.1 that should be easier/faster to use for database queries.

    js.

    Thread Starter commadelimited

    (@commadelimited)

    Thanks for the follow up JS, in case you’re interested, here’s one of the pages which uses this approach:
    https://www.meeplemountain.com/board-game-reviews-by-rating/5-stars/

Viewing 3 replies - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.