I'm starting to invest time in learning custom Drupal entities like the ones explained here and I want to make sure that I am going to be able to do something in the future.
I am working with Drupal 7 with MySQL and have the entities API. Being tied to MySQL is okay.
Is it possible to perform a query like the one listed below in Drupal? If not, can I access a MySQL database view?
select
`listProducts`.`productId` AS `productId`,
group_concat(if((`listProducts`.`className` = 'manufacturer'),
`listProducts`.`optionValue`,
'')
separator '') AS `manufacturer`,
group_concat(if((`listProducts`.`className` = 'model'),
`listProducts`.`optionValue`,
'')
separator '') AS `model`,
group_concat(if((`listProducts`.`className` = 'sub model'),
`listProducts`.`optionValue`,
'')
separator '') AS `sub model`,
group_concat(if((`listProducts`.`className` = 'year'),
`listProducts`.`optionValue`,
'')
separator '') AS `year`
from
`hqr`.`listProducts`
group by `listProducts`.`productId`
It's sort of an intense select statement. The data I'm working with is in the form
productId className optionValue
---------------------------------------
1 manufacturer abcCompany
1 model aModel
2 manufacturer xyzCompany
2 year 2005
and should come out like
productId manufacturer model sub model year
-------------------------------------------------
1 abcCompany aModel - -
2 xyzCompany - - 2005
Is doing this possible using the Drupal tools? If so, knowing how would be a big bonus too. Somewhere in my travels I saw a Drupal patch to enable group_concat in Drupal views (if that changes anything).