rauchmelder
rauchmelder

Reputation: 144

Rows are represented as columns in a View

for my sense, I've a creative way of saving userdata. Let me explain:

I do not know which data is going to be saved in this database. For example, someone wants to save his icq number and i didn't know it before, where could he write it into? He dynamically creates a new field and in background there is an insert done in fields and an insert in user_fields where the new value of the new option is stored.

Table user:

id  username
1   rauchmelder

Table fields:

id  name
1   firstname
2   lastname

Table user_fields: (old values are stored as well as current, only youngest entry should be used)

id  user_id  fields_id  value        date
1   1        1          Chris        1.Mai
1   1        2          Rauch        1.Mai
1   1        1          Christopher  2.Mai

Result should be a View:

user.id  user.username   fields.firstname  fields.lastname
1        rauchmelder     Christopher       Rauch

Firstly, does it make sense at all?

Secondly, should I solve it in MySQL or within the application?

Thridly, how to solve this in MySQL as a View?

Upvotes: 0

Views: 136

Answers (1)

Taryn
Taryn

Reputation: 247850

In order to get the data into your columns, you can use an aggregate function with a CASE expression to convert the row data into columns.

If your fields are known ahead of time, then you can hard-code the values in your query:

select u.id,
  u.username,
  max(case when f.name = 'firstname' then uf.value end) firstname,
  max(case when f.name = 'lastname' then uf.value end) lastname
from user u
left join
(
  select uf1.*
  from user_fields uf1
  inner join
  (
    select max(date) maxDate, user_id, fields_id
    from user_fields
    group by user_id, fields_id
  ) uf2
    on uf1.date = uf2.maxdate
    and uf1.user_id = uf2.user_id
    and uf1.fields_id = uf2.fields_id
) uf
  on u.id = uf.user_id
left join fields f
  on uf.fields_id = f.id
group by u.id, u.username;

See SQL Fiddle with Demo

But since you are going to have unknown fields, then you will need to use a prepared statement to generate dynamic SQL to execute. The syntax will be similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN f.name = ''',
      name,
      ''' THEN uf.value END) AS `',
      name, '`'
    )
  ) INTO @sql
FROM fields;


SET @sql 
  = CONCAT('SELECT u.id,
            u.username, ', @sql, ' 
           from user u
           left join
           (
             select uf1.*
             from user_fields uf1
             inner join
             (
               select max(date) maxDate, user_id, fields_id
               from user_fields
               group by user_id, fields_id
             ) uf2
               on uf1.date = uf2.maxdate
               and uf1.user_id = uf2.user_id
               and uf1.fields_id = uf2.fields_id
           ) uf
             on u.id = uf.user_id
           left join fields f
             on uf.fields_id = f.id
           group by u.id, u.username');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Upvotes: 3

Related Questions