1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.4 Help Writing SQL Query Statement

Discussion in 'XenForo Questions and Support' started by carino, Jan 27, 2015.

  1. carino

    carino New Member

    This is big favor to ask ...

    I have asked if there was a built-in method to export user list to Excel, and received answer that it might be easier to write an SQL query and save it to run whenever I want a listing. But, I am not very savvy with writing SQL statements.

    Hoping an SQL guru could look at what I would like and help me with the SQL syntax ...

    Two tables I would like to join ...
    1. xf_user
    2. xf_user_field_value
    Key field with both tables = user_id

    table.jpg
    Fields to retrieve from xf_user
    1. user_id
    2. username
    3. email
    4. user_state
    Fields to retrieve from xf_user_field_value
    1. user_id
    2. field_value ... where field_id is ...
      a) address
      b) firstname
      c) lastname
      d) city
      e) state
    The end result from the query that I would really like to see is this ...

    querytable.jpg

    If anyone can help me, I will buy you a cup of coffee or cold beer ... your choice.

    Thanks,

    Steve
     
  2. Rob

    Rob Well-Known Member

    SELECT
    xf_user.user_id, username, email, user_state,
    field_id, field_value
    FROM xf_user
    INNER JOIN xf_user_field_value
    ON xf_user.user_id=xf_user_field_value.user_id;

    Try that query... it should return the info you want and values of all custom fields so you may have some surplus info but this may be useful, if not, disregard that info.
    This is from the top of my head and is untested
     
  3. Valhalla

    Valhalla Well-Known Member

    You may be able to do something like this, to sort your field values into appropriate columns for exporting:

    Code:
    SELECT
       xf_user.user_id, username, email, user_state,
       MAX(CASE WHEN field_id='firstname' THEN field_value END) firstname,
       MAX(CASE WHEN field_id='lastname' THEN field_value END) lastname,
       MAX(CASE WHEN field_id='address' THEN field_value END) address,
       MAX(CASE WHEN field_id='city' THEN field_value END) city,
       MAX(CASE WHEN field_id='state' THEN field_value END) state
    FROM
       xf_user
    LEFT JOIN xf_user_field_value
       ON (xf_user.user_id=xf_user_field_value.user_id)
    GROUP BY user_id
    (I say "may" because I don't know how usable it will be for you.)
     
    Last edited: Jan 27, 2015
  4. carino

    carino New Member

    Hi Rob,

    Thanks for helping. The SQL syntax is pretty close ...


    resulting query has for each unique user_id, a row for each field_id from xf_user_field_value. The data is all there. But hoping to get the field names as column headings ..

    Here is result from running query ...

    result.jpg


    Regards,

    Steve
     
  5. Rob

    Rob Well-Known Member

    I imagine the above would need an AS clause before firstname and lastname (after the bracket on the END statement).
    Not sure why you would need to use MAX though.
     
  6. carino

    carino New Member

    Hello Valhalla,

    Thanks for pitching in. Your SQL code eliminates the multiple rows of user_id, but does not list the fields from xf_user_field_value ...

    query2.jpg


    I'g going to owe both your guys a beer!

    Regards,

    Steve
     
  7. Rob

    Rob Well-Known Member

    Can you paste the above query to save me typing it all out?
     
  8. carino

    carino New Member

    Hi Rob ... it is Valhalla's query syntax ... above.

    Thanks,

    Steve
     
  9. Rob

    Rob Well-Known Member

    Totally untested, but might work :)
    Code:
    SELECT
    xf_user.user_id,username, email,
    CASE WHEN xf_user.user_id
    THEN
        SELECT field_value FROM xf_user_field_value WHERE xf_user_field_value.user_id = xf_user.user_id AND field_id="firstname"
    END as firstname,
    CASE WHEN xf_user.user_id
    THEN
        SELECT field_value FROM xf_user_field_value WHERE xf_user_field_value.user_id = xf_user.user_id AND field_id="lastname"
    END as lastname,
    CASE WHEN xf_user.user_id
    THEN
        SELECT field_value FROM xf_user_field_value WHERE xf_user_field_value.user_id = xf_user.user_id AND field_id="address"
    END as address,
    CASE WHEN xf_user.user_id
    THEN
        SELECT field_value FROM xf_user_field_value WHERE xf_user_field_value.user_id = xf_user.user_id AND field_id="city"
    END as city,
    CASE WHEN xf_user.user_id
    THEN
        SELECT field_value FROM xf_user_field_value WHERE xf_user_field_value.user_id = xf_user.user_id AND field_id="state"
    END as state
    FROM xf_user
    INNER JOIN xf_user_field_value
    ON (xf_user.user_id=xf_user_field_value.user_id);
    There will definitly be more optimal ways of writing this query, but I guess as a report you run once in a while, performance may not be an issue
     
  10. carino

    carino New Member

    Hi Rob ... the above syntax results in error ...

    error.jpg

    Regards,

    Steve
     
  11. Rob

    Rob Well-Known Member

    I think I know a way to do this using another method, but I'm done for the day and will look at it tomorrow unless you manage to solve it.
     
  12. carino

    carino New Member

    I've been playing around with it, but my skills are low ... i'm just trying things, looking up on Google here and there, etc. I think I found another thing I suck at ... SQL coding.

    Steve.
     
  13. Valhalla

    Valhalla Well-Known Member

    I have updated my original response.
     
  14. Syndol

    Syndol Guest

    Alternatively you can use something like the following. INNER JOIN ensures that column has a value but if you are not concerned about empty values then use LEFT JOIN

    Code:
    SELECT user.user_id, username, user_field_first_name.field_value AS first_name, user_field_last_name.field_value AS last_name, email, user_state, user_field_address.field_value AS address, user_field_city.field_value AS city, user_field_state.field_value AS state
    FROM xf_user AS user
    INNER JOIN xf_user_field_value AS user_field_address ON (user_field_address.user_id = user.user_id) AND user_field_address.field_id = 'address'
    INNER JOIN xf_user_field_value AS user_field_first_name ON (user_field_first_name.user_id = user.user_id) AND user_field_first_name.field_id = 'firstname'
    INNER JOIN xf_user_field_value AS user_field_last_name ON (user_field_last_name.user_id = user.user_id) AND user_field_last_name.field_id = 'lastname'
    INNER JOIN xf_user_field_value AS user_field_city ON (user_field_city.user_id = user.user_id) AND user_field_city.field_id = 'city'
    INNER JOIN xf_user_field_value AS user_field_state ON (user_field_state.user_id = user.user_id) AND user_field_state.field_id = 'state'
     
    Rob likes this.
  15. carino

    carino New Member

    Valhalla and Syndol ... you guys rock! Works perfectly. I owe you guys coffee ...

    Rob ... I owe you coffee as well.

    If you can send me a private conversation of your email, I have a Starbucks e-gift card for you.

    Thanks,

    Steve
     
  16. Rob

    Rob Well-Known Member

    SELECT address,
    Wasn't aware you could multiple join on the same table! Thanks for that Syndol :) SQL isnt my strong point
     

Share This Page