WordPress.org

Ready to get started?Download WordPress

Forums

Exports and Reports
Help joining queries (7 posts)

  1. CoastAMS
    Member
    Posted 2 years ago #

    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:

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

    If anyone can help - I would be really grateful.
    Thanks

    http://wordpress.org/extend/plugins/exports-and-reports/

  2. Scott Kingsley Clark
    Member
    Plugin Author

    Posted 2 years ago #

    You could try this:

    SELECT
    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'
  3. CoastAMS
    Member
    Posted 2 years ago #

    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?
    eg:
    post_title ¦ reg_no_value ¦ price_value ¦ color_value ¦ year_value

    thanks

  4. Scott Kingsley Clark
    Member
    Plugin Author

    Posted 2 years ago #

    SELECT
    p.post_title,
    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'
  5. CoastAMS
    Member
    Posted 2 years ago #

    Thank you again - unfortunately that doesn't appear to work just returns a 'No items found' message

  6. CoastAMS
    Member
    Posted 2 years ago #

    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?

    SET SESSION SQL_BIG_SELECTS=1;
    SELECT
    post_title,
    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'
    ORDER BY
    ID
  7. Scott Kingsley Clark
    Member
    Plugin Author

    Posted 2 years ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic