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.
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 ----------------------------------------- -------------------------------------------------------------- This message may contain confidential or privileged information, and is intended only for the addressee or other persons entitled to access the information. If you are not the intended or authorised recipient of this message, please notify the sender by return and then delete it from your mailbox. Any dissemination, distribution, copying or use of this communication without prior permission is prohibited. Internet communications are not secure and may be susceptible to data corruption, interception, and unauthorised amendment for which Pall Europe Limited does not accept liability. Whilst we have taken all reasonable precautions to ensure that this e-mail and any attachments have been swept for viruses, Pall Europe Limited does not accept liability for losses sustained as a result of software viruses. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Pall Europe Limited unless otherwise specifically stated. Registered Office and Corporate Headquarters: Pall Europe Limited, Europa House, Havant Street, Portsmouth, PO1 3PD. Registered No 769075, England. --------------------------------------------------------------
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.
On 10/4/06, Drupal Indonesia <support@drupal-id.com> wrote:
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.
If you have a working query, don't worry about the future. Donald Knuth: "Premature optimization is the root of all evil" Wait until the query is a problem and then we can look into optimizing it. When you have 1000 users then this query will probably not be the bottleneck - something else will - so you should optimize the other thing. Are you using this in a block? If so, use block_cache to cache the result. Regards, Greg -- Greg Knaddison | Growing Venture Solutions Denver, CO | http://growingventuresolutions.com Technology Solutions for Communities, Individuals, and Small Businesses
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.
larry white wrote:
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.
-------------------------------------------------
$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));
doh. slight correction. the where condition for 'IN' must be inside parentheses. a couple of ways to do this (one or the other, not both): 1). change: $blog_uids_where_str=implode(',',$blog_uids_array); to $blog_uids_where_str='('.implode(',',$blog_uids_array).')'; or 2). change: $outer_query="SELECT count(u.name) FROM {users} u WHERE u.uid IN ".$blog_uids_where_str; to $outer_query="SELECT count(u.name) FROM {users} u WHERE u.uid IN (".$blog_uids_where_str.")"; -- larz
----- 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.
here I found a solution! SELECT DISTINCT uid, @test:=0, @test := @test+ 1 FROM node WHERE type='blog' GROUP BY uid ORDER BY @test DESC LIMIT 1
participants (4)
-
Adam Cooper -
Drupal Indonesia -
Greg Knaddison - GVS -
larry white