0

I googled this case for more than a week, I didn't get any correct answers

I have a post type called listing.

This listing have a rating stored in wp_postmeta table, the stored meta_key is lp_listingpro_options and the meta_value is an array like this

a:2:{s:6:"rating";s:1:"5";s:10:"listing_id";s:2:"27";}
a:2:{s:6:"rating";s:1:"4";s:10:"listing_id";s:2:"27";}
a:2:{s:6:"rating";s:1:"3";s:10:"listing_id";s:2:"27";}

the previous 3 values means that (where the listing id = 27 there is 3 ratings, 5,4,3

What is the best way to do the following queries

  1. Count number of ratings (I didn't find any ideas about how to start the query)
  2. Top rated posts "most rated" (I didn't find any ideas about how to start the query)
  3. Get rating that get 5 rating score

for solving the third item and getting listing that got 5 rating I did the following query

  $args =array(
    'orderby' => 'rand',
    'post_type' => 'listing',
    'post_status' => 'publish',
    'posts_per_page' => 6,
    'meta_query' => array(
          array(
              'key'     => 'lp_listingpro_options',
              'value'   => array(
                  'key'     => 'rating',
                  'value'   => [5],
                  'compare' => '=',
              )
          ),
    ),
  );
  • Do you have multiple ratings stored using the same key for the same listing? – NightHawk May 2 '18 at 16:19
  • 1
    The fundamental issue is that you have a PHP data construct (serialized array), which you are trying to query with MySQL, which sees this as a string of random characters with no meaning. – Milo May 2 '18 at 16:21
  • @NightHawk the same key lp_listingpro_options used to store all ratings with the associated listing_id – Abdallah Sabri May 2 '18 at 17:06
  • Since the post ID (listing ID) is already stored with the post meta in the post_id field, you don't need that value in the serialized array. That leaves you with just the rating, which you can store as a single integer. If you can get it into that format, the three pieces of information are far easier to obtain. – NightHawk May 2 '18 at 18:21
  • There is listing => review => rating The post_id is related to review associated with that listing The second point I bought this theme from theme forest, I will edit it only – Abdallah Sabri May 3 '18 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.