php - MySQL EAV SELECT one-to-many single row results -
i've seen similar questions on here, haven't been able find 1 matches specific scenario. have following 3 tables:
content id, [other fields] attributes id, title attributes_values id, attribute_id[foreign attributes.id], content_id[foreign content.id], value
what i'd single query (or group of sub-queries) can return appropriate data avoid having manipulate in programming.
a little more info tables: attributes has 3 records (model #, part #, , show in cart).
i need select records content attribute_value show in cart = 1. along this, i'd other related attribute_values returned related attribute.title. results this:
id [other fields] model # [from attributes.title field] part # [from attributes.title field] 1 [any data] value attributes_values value attributes_values 2 [any data] value attributes_values value attributes_value
so far, way of accomplishing not elegant i'm having multiple joins same table:
select * content left join attributes_values b on (b.content_id = a.id , b.attribute_id = [model # id]) left join attributes_values c on (c.content_id = a.id , c.attribute_id = [part # id]) a.id in ( select content_id attributes_values attribute_id = [show in cart id] , value = 1 )
as can see, i'm having hard-code related keys join statements, doesn't make scalable (and feels dirty).
some last few notes: i'm using php , joomla!, if affect answer (or if there's secret joomla tip), feel free include it. also, pre-established table schema i'm not able alter, need query solution above mentioned tables, not suggestion on better table design.
any appreciated.
cheers!
instead of joining table multiple times each attribute, can use aggregated function max() in combination case when statement:
select content.id, [other_fields], max(case when attributes.title='model #' attributes_values.value end) model_n, max(case when attributes.title='part #' attributes_values.value end) part_n content inner join attributes_values on content.id = attributes_values.content_id inner join attributes on attributes_values.attribute_id = attributes.id group id, [other_fields] having max(case when attributes.title='show in cart' attribute_values.value end)='1'
a little explanation:
case when attributes.title='model #' attributes.value end
will return value when attribute 'model #' , null otherwise, and
max(...)
will return maximum non-null value, value attributes.title='model #'.
dynamic attributes
mysql not have pivot function, if want list dynamic, have dinamically create sql string attributes , execute string.
you can fetch attributes attributes
table:
select concat( 'max(case when attributes.title=''', replace(attributes.title, '''', ''''''), ''' attributes_values.value end) `', replace(attributes.title, '`', '``'), '`' ) attributes;
and combine group_concat:
select group_concat(...the concat above...) attributes;
so final query this:
select concat('select content.id,', group_concat( concat( 'max(case when attributes.title=''', replace(attributes.title, '''', ''''''), ''' attributes_values.value end) `', replace(attributes.title, '`', '``'), '`' ) ), ' content inner join attributes_values ', 'on content.id = attributes_values.content_id ', 'inner join attributes ', 'on attributes_values.attribute_id = attributes.id ', 'group id having ', 'max(case when attributes.title=''show in cart'' attributes_values.value end)=''1''' ) attributes @sql;
now @sql variable hold value of query executed, , can execute with:
prepare stmt @sql; execute stmt;
the problem here group_concat has maximum length limit, can increase if table holding many attributes.
please see example fiddle here.
Comments
Post a Comment