XF 1.4 Help Writing SQL Query Statement

[carino]

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


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 ...



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

Thanks,

Steve

 

[Rob]

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

 

[Valhalla]

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

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.)

 

[carino]

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 ...




Regards,

Steve

 

[Rob]

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.

 

[carino]

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 ...




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

Regards,

Steve

 

[Rob]

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

 

[carino]

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

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

Thanks,

Steve

 

[Rob]

Totally untested, but might work

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

 

[carino]

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



Regards,

Steve

 

[Rob]

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.

 

[carino]

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.

 

[Valhalla]

I have updated my original response.

 

[Syndol]

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

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'

 

[carino]

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

 

[Rob]

SELECT address,

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

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'

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