Abfrage von user_custom_fields

[slackmoehrle]

Unsere Marketingabteilung benötigt einige Daten. Ich kann Discourse abfragen, um diese zu erhalten. Allerdings habe ich Probleme mit user_custom_fields: Wir haben mehrere benutzerdefinierte Felder, und jedes scheint pro Benutzer eine Zeile in der Tabelle zu sein. Ich könnte also 6–7 Zeilen pro Benutzer haben. Ich arbeite an einem Join, aber dabei bekomme ich viele Zeilen pro Datensatz, sodass ich herausfinden muss, wie man die Abfrage korrekt gestaltet.

Hier ist mein bisheriger Ansatz:

SELECT
    u.id,
    u.username_lower AS "username",
    u.created_at,
    u.last_seen_at,
    u.ip_address,
    ue.email,
    (SELECT COUNT(*)
        FROM   user_badges ub
        WHERE ub.user_id = u.id
        ) AS badge_count
FROM users u
LEFT OUTER JOIN user_emails ue on u.id = ue.user_id
LEFT OUTER JOIN user_custom_fields ucf on u.id = ue.user_id
WHERE u.active = true
AND u.username_lower='slackmoehrle' 
ORDER BY u.id;

So sieht user_custom_fields aus:

Idealerweise möchte ich pro Benutzer einen einzigen Datensatz, der die gewünschten Felder sowie die Werte der Zeilen in user_custom_fields anzeigt.

Hilfe beim Finden der richtigen Joins/Syntax wäre toll!

[simon]

You could try using WITH queries to define temporary tables for your user custom fields. As an example, I have User Fields for phone number and address. I know that the phone number field is user_field_1 in my database and address is user_field_2. Here’s a query that will return the user’s email address, phone number, and street address, with one row for each user:

WITH user_field_1 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_1'
),
user_field_2 AS (
SELECT ucf.value,
ucf.user_id
FROM user_custom_fields ucf
WHERE ucf.name = 'user_field_2'
)

SELECT
u.id AS user_id,
ue.email,
uf1.value AS phone_number,
uf2.value AS address
FROM users u
LEFT JOIN user_field_1 uf1
ON uf1.user_id = u.id
LEFT JOIN user_field_2 uf2
ON uf2.user_id = u.id
JOIN user_emails ue
ON ue.user_id = u.id

The easiest way I know of to find the name value of your user fields is to view the json of your user fields page (/admin/customize/user_fields.json). You’ll see the id for each field in the json data. A field with the id of 1 creates a user_custom_field with the name user_field_1. A field with the id of 2 creates a user_custom_field with the name user_field_2.

[slackmoehrle]

This looks promising. I will work with it and see how I make out. Thank you for taking time out of your day to answer my post.

EDIT: This is the perfect solution. I integrated this to my existing work and things are performing great.