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

Popular posts from this blog

c# - SharpSsh Command Execution -

python - Specify path of savefig with pylab or matplotlib -

How to run C# code using mono without Xamarin in Android? -