3

I'm still not comfortable writing sql queries. As i'm pretty new to drupal module writing.

Are there any potential issues with using views to generate a sql query? In terms of security or future considerations?

Example: Generate a form dropdown on the usermenu of number of users by role.

Grabbed this query via creating a view

  $query = "SELECT users.uid AS uid,
   users.mail AS users_mail,
   users.name AS users_name
   FROM users users 
   INNER JOIN users_roles users_roles ON users.uid = users_roles.uid
   WHERE users_roles.rid = 7";

Convert database object into array:

while ($user = db_fetch_object($result)) { 
  $reps[$user->uid] = $user->users_name . ' - ' . $user->users_mail;
}

Spit out the options and create a form dropdown:

// Generate output of all the client reps
$form['Business']['profile_txi_account_rep']['#options'] =  $reps;
4

The only real downside that I can think of is that the SQL that is generated can be really verbose, as it is not meant to be read by humans.

Very often, I will take a first pass at functionality for something using Views, grab the SQL, and then refine/optimize. In fact, using Views to generate the initial SQL can be very handy for figuring out what you need to do your JOINs on.

One catch is that Views knows about internal structure. If you do something that changes underlying table structure, Views will compensate automagically. You would need to update your SQL manually. One place this happens is with Drupal 6 and CCK, when you change fields from single value on a single content type to multiple value and/or add them to multiple content types.

All of that said, the new database abstraction layer and EntityFieldQuery does make life easier, and you may not need to use Views as an intermediate step.

  • On a high level, any advice in terms of where to usually look in order to optimize the particular query? – chrisjlee Feb 8 '12 at 22:37
  • 1
    It's hard to be really specific, but you can sometimes eliminate JOINs and/or take advantage of knowledge about the dataset to improve the query. You can also sometimes convert LEFT JOINs into INNER JOINs if you know your data, and this can reduce the size of the result set. – mpdonadio Feb 8 '12 at 23:50

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.