Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

In my WP database I have some posts i the category hotels and some other posts in the category visits. In the posts (placed in the category visits) there is two meta_fields ( rating and placeid ) rating is the rating for the visit and placeid is the id (post_ID) for the hotel ( a post in the category hotels).

Now I will want to make an average of the ratings for the hotel. I will do it in one MySQL-question.

How can I join and calculate the average on the right way?

Here is my code where I get the hotels:

$result = query("SELECT p.*
        , x.adresse
        , x.lat
        , x.lon
        , x.location
            , y.rating
            , y.placeid
        FROM wp_posts p
        LEFT
        JOIN 
            ( SELECT post_id
                    , MAX(CASE WHEN meta_key = 'adresse' THEN meta_value END) adresse
                    , MAX(CASE WHEN meta_key = 'lat'  THEN meta_value END) lat
                    , MAX(CASE WHEN meta_key = 'lon'  THEN meta_value END) lon
                    , MAX(CASE WHEN meta_key = 'location'  THEN meta_value END) location
                 FROM wp_postmeta
                GROUP
                   BY post_id
             ) x 
        ON x.placeid = p.ID
            LEFT
        JOIN 
            ( SELECT post_id
                    , MAX(CASE WHEN meta_key = 'rating' THEN meta_value END) rating
                    , MAX(CASE WHEN meta_key = 'placeid'  THEN meta_value END) placeid
                 FROM wp_postmeta
                GROUP
                   BY post_id
             ) y 
        ON y.post_id = p.ID
        LEFT 
        JOIN 
            ( SELECT * FROM wp_term_relationships ) r
        ON (p.ID = r.object_id)
        LEFT 
        JOIN 
            ( SELECT * FROM wp_term_taxonomy ) q
        ON (r.term_taxonomy_id = q.term_taxonomy_id)
        LEFT 
        JOIN 
            ( SELECT * FROM wp_terms ) t
        ON (q.term_id = t.term_id)
     WHERE p.post_status = 'publish' AND t.name = 'Hotels' AND q.taxonomy = 'category'
  ORDER BY ID DESC ");

I also got these average-code - but can't implement it on my code. I'm stuck!

SELECT AVG(rating) as gennemsnit FROM anmeldelser WHERE placeid=$postid
share|improve this question
    
As written, this is a pure SQL question question which is arguably off-topic. 1) Why are you not using a Core method like WP_Query? and 2) take a look at this related question/answer –  s_ha_dum Feb 23 at 18:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.