Support » Plugins and Hacks » Exports and Reports » [Plugin: Exports and Reports] Help joining queries

[Plugin: Exports and Reports] Help joining queries

  • Hi,

    I have no experience of SQL – but I’m sure this plugin will do what I need. I just don’t think I have the required brain power to get it to work 🙂

    We’re using WordPress to list vehicles for sale. I would like to be able to generate a report of the vehicles listed.

    So far I have got the individual queries working (see code below)… but I need to join them together into one query/report

    My queries:

    FROM wp_posts
    WHERE post_type="listing"
    FROM wp_postmeta WHERE meta_key = 'regno_value'
    FROM wp_postmeta WHERE meta_key = 'price_value'
    FROM wp_postmeta WHERE meta_key = 'color_value'
    FROM wp_postmeta WHERE meta_key = 'year_value'

    If anyone can help – I would be really grateful.


Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Scott Kingsley Clark


    You could try this:

    post_title, meta_key, meta_value
    FROM wp_posts
    LEFT JOIN wp_postmeta ON post_id = wp_posts.ID AND meta_key IN ('regno_value', 'price_value', 'color_value', 'year_value')
    WHERE wp_posts.post_type = 'listing'

    Thank you for getting back to me so quickly. That’s ALMOST it…

    The only issue is it displays each meta key on a separate row – so each van is displayed 4 times (screenshot: http://twitpic.com/6rbdg8/full )

    is there anyway to get it so that it displays the data in the same row?
    post_title ¦ reg_no_value ¦ price_value ¦ color_value ¦ year_value


    Plugin Author Scott Kingsley Clark


    m1.meta_value AS regno_value,
    m2.meta_value AS price_value,
    m3.meta_value AS color_value,
    m4.meta_value AS year_value,
    FROM wp_posts AS p
    LEFT JOIN wp_postmeta AS m1 ON m1.post_id = p.ID AND m1.meta_key = 'regno_value'
    LEFT JOIN wp_postmeta AS m2 ON m2.post_id = p.ID AND m2.meta_key = 'price_value'
    LEFT JOIN wp_postmeta AS m3 ON m3.post_id = p.ID AND m3.meta_key = 'color_value'
    LEFT JOIN wp_postmeta AS m4 ON m4.post_id = p.ID AND m4.meta_key = 'year_value'
    WHERE p.post_type = 'listing'

    Thank you again – unfortunately that doesn’t appear to work just returns a ‘No items found’ message



    I’ve managed to get the query working.

    However although this works in the mySQL dashboard it doesn’t work in the plugin.

    Is there anything else I need to/can do to make this work in the plugin?

    regno.meta_value as regno,
    price.meta_value as price,
    color.meta_value as color,
    year.meta_value as year,
    mileage.meta_value as mileage,
    enginesize.meta_value as enginesize,
    doors.meta_value as doors,
    trans.meta_value as trans,
    fueltype.meta_value as fueltype,
    owners.meta_value as owners,
    body_type.meta_value as body_type
    FROM wp_posts
    LEFT JOIN wp_postmeta AS regno ON regno.post_id=ID AND regno.meta_key='regno_value'
    LEFT JOIN wp_postmeta AS price ON price.post_id=ID AND price.meta_key='price_value'
    LEFT JOIN wp_postmeta AS color ON color.post_id=ID AND color.meta_key='color_value'
    LEFT JOIN wp_postmeta AS year ON year.post_id=ID AND year.meta_key='year_value'
    LEFT JOIN wp_postmeta AS mileage ON mileage.post_id=ID AND mileage.meta_key='mileage_value'
    LEFT JOIN wp_postmeta AS enginesize ON enginesize.post_id=ID AND enginesize.meta_key='enginesize_value'
    LEFT JOIN wp_postmeta AS doors ON doors.post_id=ID AND doors.meta_key='doors_value'
    LEFT JOIN wp_postmeta AS trans ON trans.post_id=ID AND trans.meta_key='trans_value'
    LEFT JOIN wp_postmeta AS fueltype ON fueltype.post_id=ID AND fueltype.meta_key='fueltype_value'
    LEFT JOIN wp_postmeta AS owners ON owners.post_id=ID AND owners.meta_key='owners_value'
    LEFT JOIN wp_postmeta AS body_type ON body_type.post_id=ID AND body_type.meta_key='body_type_value'
    WHERE wp_posts.post_type = 'listing'
    AND post_status = 'publish'
    Plugin Author Scott Kingsley Clark


    You could configure your MySQL.ini settings to allow big selects, otherwise I’d have to make some changes to this plugin to allow for that. The way it runs it’s queries is specific and it does rewriting on the query to handle pagination / filtering too, so there’s a lot in the mix.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘[Plugin: Exports and Reports] Help joining queries’ is closed to new replies.