----- Original Message ----- From: "larry white" larz@openflows.com To: support@drupal.org Sent: Thursday, October 05, 2006 2:27 AM Subject: Re: [support] using drupal in mysql without subquery
Drupal Indonesia wrote:
In Drupal installation that using MySQL without subquery support, what is the right sql statement for this query?
SELECT count(u.name) FROM {users} u WHERE u.uid IN (SELECT uid from {node} n where type='blog')
Above query is show number of users who whas written Blog.
sorry, if this ends up being a duplicate message. not sure what happened when i tried to submit it to the list earlier today, but it did not appear to added to the thread.
been a while since i've done this type of thing. the approach i have used in the past involves a multi-step process:
- run subquery as a separate query first
- loop through results of separate query and place these values into an
array 3) use 'implode' to convert the array to a string, with each value separated by a comma ','. 4) use the string containing the values as a replacement for the subquery
$inner_query="SELECT uid from {node} n where type='blog'";
$blog_uids_res=db_query($inner_query);
while ($blog_uids=db_fetch_object($blog_uids_res)) { $blog_uids_array[]=$blog_uids->uid; }
$blog_uids_where_str=implode(',',$blog_uids_array);
$outer_query="SELECT count(u.name) FROM {users} u WHERE u.uid IN ".$blog_uids_where_str;
$blog_user_count=db_result(db_query($outer_query));
btw, step 2 is drupal-specific. i would prefer to put these values into an array immediately, as opposed to looping through individual results. perhaps i've missed something along the way, but is there a way in drupal to pull out the entire result set as an array. would be useful in a situation like this, where you're only grabbing data from one column. would be nice to have it all in an array to implode immediately, instead of having to loop through it. for those familiar with php 'pear' db extension, think the 'getCol()' function.
your tip is not efficient in my pointof view, because as I mention, I already use DISTINCT, so the current query that I already used was: $inner_query="SELECT DISTINCT uid from {node} n where type='blog'"; so I can avoid implode and redundant rows. then use Drupal "count rows" function.
I think, I will follow Greg Knaddison's suggestions to use cache, while still find the possibility to make a very efficient query.