[support] using drupal in mysql without subquery

[Drupal Indonesia]

----- Original Message ----- 
From: "larry white" <larz at openflows.com>
To: <support at 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:
>
> 1) run subquery as a separate query first
> 2) 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.