4

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).

1

Drupal does not provide database transposition, unfortunately. It's database access system is pretty good at filtering, joining and reordering the data, but not at restructuring them.

There are many patches and hacks for specific task, but none was general enough and needed enough to be accepted into core or views so far.

Your safest bet is to define a view, and then access it just like any other table. Drupal sees no difference as long as you don't write (and if you want to play, you could make MySQL to accept writes, too).

0

Yes you can easily reproduce that query using Drupal's DB API. In fact you run pretty much any valid MySQL query through that API; the only type I've had problems with in the past is LOAD DATA INFILE.

The key is the SelectQuery::addExpression() method, which allows you to inject arbitrary expressions into the query. For example:

$query = db_select('listProducts')->groupBy('listProducts.productId');

foreach (array('manufacturer', 'model', 'sub model', 'year') as $field_name) {
  $sql = "group_concat(if((listProducts.className = '$field_name'), listProducts.optionValue, '') separator '')";
  $query->addExpression($sql, $field_name);
}

$results = $query->execute();

That produces:

SELECT 
  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 submodel, 
  group_concat(if((listProducts.className = 'year'), listProducts.optionValue, '') separator '') AS year
FROM {listProducts} listProducts
GROUP BY listProducts.productId

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.