I'm working with the following data structure:
users
| field| value
| --- | ---
| id | 123
| email| example@example.com
fields
| field |value
| --- |---
| parentId |123 -> users.id
| name |Bank
| value |WBC
Users have a one-to-many relationship to fields, a single user will have multiple fields with different names.
I'm trying to construct a query that would ultimately output something like:
| field | value
|--- |---
| id |123
| email |example@example.com
| bank |ANZ
So there's a new column added to the query for each unique name value. I do know all the possible name values up front.
My query is pretty standard so far, it's grouping but obviously grouping discards all but 1 of the joined model. Is there a way around this?
SELECT users.id, fields.name
FROM users
LEFT JOIN fields ON (fields.parent_id= users.id)
GROUP BY users.id
ORDER BY updated_at DESC
LIMIT 100 OFFSET 0
Best Answer
Your question appears to be a case of on the fly fields ( dynamic attributes ), which means that within your application end users can add information about the user on the fly, if this is not the case you need to reconsider schema design.
Here is the query that can be used to achieve the result, here you must know all the fields in advance:
also consider field updated_at is not mentioned by you in your example. I am assuming it is available only in one table that should be the users.