...

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.

Use a join:

SELECT count(u.name) FROM {users} u JOIN {node} n ON u.uid = n.uid WHERE n.type = 'blog';

Thanks Adam

your query is not work. above query return all row with type='blog'. what do I need is the number of users. look at this example: if user A create 5 blogs, user B creates 3 blogs, and ser C creates 0 blog, then: 1. original query with subquery support, returns: 2 (this is the right answer) 2. your query, return: 8.

currently, I solve this problem by take all row using DISTINCT then use "Drupal db count" to count the rows, but this is not efficient, because when my db contains 1.000 users then the results is 1.000 rows, what do I need only 1 row that contain "1000" value.

----- 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:

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.