How to retrieve "dynamic" attributes stored in multiple rows as normal records?

Question

I have a system built on a relational MySQL database that allows people to store details of "leads". In addition, people can create their own columns under which to store data and then when adding new accounts can add data under them. The table structure looks like this:

LEADS - id, email, user_id

ATTRIBUTES - id, attr_name, user_id

ATTR_VALUES - lead_id, attr_id, value, user_id

Obviously in these tables "user_id" refers to a "Users" table that just contains people that can log into the system.

I am writing a function to output lead details and currently am just pulling through the basic lead details as a query, and then pulling through every attribute value associated with that lead (joining on the attributes table to get the name) and then joining the arrays in PHP. This is a little messy, and I was wondering if there was a way to do this in one SQL query. I have read a little about something called a "pivot table", but am struggling to understand how it works.

Any help would be greatly appreciated. Thanks!

Answer 1

You could do the pivoting in a single query like the following:

select l.id lead_id,
       l.email,
       group_concat(distinct case when a.attr_name = 'Home Phone' then v.value end) HomePhone,
       ...
from leads l
left join attr_values v on l.id = v.lead_id
left join attributes a on v.attr_id = a.id
group by l.id

You will need to include a separate group_concat-derived field for each attribute you want to display.

Answer 2

I'll be careful to assume that pivot will achieve your simplification goal. Pivot will only work if you attr_name are consistent. Since you tied a userid to it, I assume it wouldn't. In addition, you will have multiple values for one attr_name. I'm afraid pivot table wouldn't produce the result you are looking for.

I would suggest that you keep your transactional and reporting tables separate. Have an ETL routine that will clean (ie. make the attr_name and attr_value) consistent through translation. This will make your reports more meaningful.

In summary, for immediate output to end-user, PHP is the best you can do. For reporting, transform the EAV to a row/column first before attempting to report on it.

Answer 3

I would have a look at this link. That explain the fundamental of a pivot:

"pivot table" or a "crosstab report" SQL Characteristic Functions: Do it without "if", "case", or "GROUP_CONCAT". Yes, there is use for this..."if" statements sometimes cause problems when used in combination. The simple secret, and it's also why they work in almost all databases, is the following functions: sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0 1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0

It a also explain a more simple way of pivoting exams. Maybe this can shed some light over it?

Answer 4

What you probably want from mysql is to make an sql value (attr_name in your case) a column. This principle is called pivot table (sometimes also cross tables or crosstab queries) and is not supported by mysql. Not because mysql is insufficient, but because the pivot operation is not a database operation - the result is not a normal database table and is not designed for further database operations. The only purpose of pivot operation a presentation - that's why it belongs to presentation layer, not database.

Thus, every solution of trying to get a pivot table from mysql will always be hacky. What I recommend is to get the data from database in normal format, by simply doing something like:

select *
from attr_values join attributes using on attr_id = attributes.id
    join leads on leads.id = lead_id

and then transform the database output in the presentation language (PHP, JSP, Python or whatever you use).