SQL to merge related documents into 1 query row (MySQL)

Question

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

Answer 1

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:

SELECT users.id, users.email, group_concat(IF(fields.name='Bank',fields.value,'')) Bank
FROM users 
LEFT JOIN fields ON (fields.parent_id= users.id) 
GROUP BY users.id 
ORDER BY updated_at DESC 
LIMIT 100 OFFSET 0

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.