I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlan neil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlan <neil@esl-lounge.com mailto:neil@esl-lounge.com> wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view. At the moment, the view is only taking the first one. so, here is a simplified version of my php code: global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid"; on my current db, this returns 3 UIDs...the view only takes the first one. Thanks Neil -- [ Drupal support list | http://lists.drupal.org/ ]
Take a look at this code I used for something similar:
if (arg(0) == 'node' && is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlan neil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node' && is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
*From:*support-bounces@drupal.org [mailto:support-bounces@drupal.org] *On Behalf Of *Neil Coghlan *Sent:* Saturday, November 20, 2010 3:25 PM *To:* support@drupal.org *Subject:* Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlan <neil@esl-lounge.com mailto:neil@esl-lounge.com> wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 5:20 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node' && is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlan neil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
this is where I'm up to
$result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM node WHERE node.uid IN (SELECT node.uid FROM node WHERE nid IN (SELECT nid FROM content_type_profile WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile JOIN node ON content_type_profile.nid = node.nid WHERE node.uid = 2 AND node.type = 'profile'))) AND node.uid != 2"); $member = db_fetch_object($result); while($member = db_fetch_object($result)){ return $member->uid . "+"; }
I expect it to come back "31+37+39+" but it's coming back only "37+" (if I test in on a page and use "print" instead of "return", I get exactly what I want)
that would be my woeful php obviously for which I apologise now.
I'm trying to adapt what Idan wrote but cannot get it to output multiple UIDs to insert as the user:uid argument.
I'm also getting Query Build Time in views of around 5050ms so I'm guessing my sql is massively inefficient too...I do need the join and the nested SELECTs though. I'm getting the UID of all the people who share the location taxonomy term on their profile with the logged in user, so it was never going to be easy!
by the way, in my query, I'm hardcoding the logged in user's UID (2) just for testing purposes.
Neil
On 20/11/2010 12:23, Idan Arbel wrote:
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
*From:*support-bounces@drupal.org [mailto:support-bounces@drupal.org] *On Behalf Of *Neil Coghlan *Sent:* Saturday, November 20, 2010 5:20 PM *To:* support@drupal.org *Subject:* Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node' && is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
*From:*support-bounces@drupal.org mailto:support-bounces@drupal.org [mailto:support-bounces@drupal.org] *On Behalf Of *Neil Coghlan *Sent:* Saturday, November 20, 2010 3:25 PM *To:* support@drupal.org mailto:support@drupal.org *Subject:* Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlan <neil@esl-lounge.com mailto:neil@esl-lounge.com> wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
Neil,
If you use return inside the while loop like you do it, the loop will process the first return of the query (37), and return that value to the parent function. It basically will break the loop and won't go through it until the further entries are processed.
You need to concatenate the fetched values to a variable (for example), and return that variable once the loop is finished. while($member = db_fetch_object($result)){ $variable.= $member->uid . "+"; } return $variable;
If you want to retrieve the values from the database ordered, you need to add an "order by node.uid asc" to the SQL query.
Hope this helps, Ursula
On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlan neil@esl-lounge.com wrote:
this is where I'm up to
$result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM node WHERE node.uid IN (SELECT node.uid FROM node WHERE nid IN (SELECT nid FROM content_type_profile WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile JOIN node ON content_type_profile.nid = node.nid WHERE node.uid = 2 AND node.type = 'profile'))) AND node.uid != 2"); $member = db_fetch_object($result); while($member = db_fetch_object($result)){ return $member->uid . "+"; }
I expect it to come back "31+37+39+" but it's coming back only "37+" (if I test in on a page and use "print" instead of "return", I get exactly what I want)
that would be my woeful php obviously for which I apologise now.
I'm trying to adapt what Idan wrote but cannot get it to output multiple UIDs to insert as the user:uid argument.
I'm also getting Query Build Time in views of around 5050ms so I'm guessing my sql is massively inefficient too...I do need the join and the nested SELECTs though. I'm getting the UID of all the people who share the location taxonomy term on their profile with the logged in user, so it was never going to be easy!
by the way, in my query, I'm hardcoding the logged in user's UID (2) just for testing purposes.
Neil
On 20/11/2010 12:23, Idan Arbel wrote:
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 5:20 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node' && is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlan neil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
Or, to avoid a stray '+' at the end,
$members = array(); while( $member = db_fetch_object($result) ){ $members[] = $member->uid; }
return implode('+', $members);
On 11/24/2010 01:29 PM, Ursula Pieper wrote:
Neil,
If you use return inside the while loop like you do it, the loop will process the first return of the query (37), and return that value to the parent function. It basically will break the loop and won't go through it until the further entries are processed.
You need to concatenate the fetched values to a variable (for example), and return that variable once the loop is finished. while($member = db_fetch_object($result)){ $variable.= $member->uid . "+"; } return $variable;
If you want to retrieve the values from the database ordered, you need to add an "order by node.uid asc" to the SQL query.
Hope this helps, Ursula
On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlanneil@esl-lounge.com wrote:
this is where I'm up to
$result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM node WHERE node.uid IN (SELECT node.uid FROM node WHERE nid IN (SELECT nid FROM content_type_profile WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile JOIN node ON content_type_profile.nid = node.nid WHERE node.uid = 2 AND node.type = 'profile'))) AND node.uid != 2"); $member = db_fetch_object($result); while($member = db_fetch_object($result)){ return $member->uid . "+"; }
I expect it to come back "31+37+39+" but it's coming back only "37+" (if I test in on a page and use "print" instead of "return", I get exactly what I want)
that would be my woeful php obviously for which I apologise now.
I'm trying to adapt what Idan wrote but cannot get it to output multiple UIDs to insert as the user:uid argument.
I'm also getting Query Build Time in views of around 5050ms so I'm guessing my sql is massively inefficient too...I do need the join and the nested SELECTs though. I'm getting the UID of all the people who share the location taxonomy term on their profile with the logged in user, so it was never going to be easy!
by the way, in my query, I'm hardcoding the logged in user's UID (2) just for testing purposes.
Neil
On 20/11/2010 12:23, Idan Arbel wrote:
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 5:20 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node'&& is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlanneil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
Ursula/Christopher,
Thanks for this help. I'll try it later when I'm at home. The only thing that concerns me is that views was showing 5000ms for that view to render so I suspect having such a complicated nested sql query in there just to supply the arg is going to be a killer.
Neil
On 24/11/2010 15:42, Christopher M. Jones wrote:
Or, to avoid a stray '+' at the end,
$members = array(); while( $member = db_fetch_object($result) ){ $members[] = $member->uid; }
return implode('+', $members);
On 11/24/2010 01:29 PM, Ursula Pieper wrote:
Neil,
If you use return inside the while loop like you do it, the loop will process the first return of the query (37), and return that value to the parent function. It basically will break the loop and won't go through it until the further entries are processed.
You need to concatenate the fetched values to a variable (for example), and return that variable once the loop is finished. while($member = db_fetch_object($result)){ $variable.= $member->uid . "+"; } return $variable;
If you want to retrieve the values from the database ordered, you need to add an "order by node.uid asc" to the SQL query.
Hope this helps, Ursula
On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlanneil@esl-lounge.com wrote:
this is where I'm up to
$result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM node WHERE node.uid IN (SELECT node.uid FROM node WHERE nid IN (SELECT nid FROM content_type_profile WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile JOIN node ON content_type_profile.nid = node.nid WHERE node.uid = 2 AND node.type = 'profile'))) AND node.uid != 2"); $member = db_fetch_object($result); while($member = db_fetch_object($result)){ return $member->uid . "+"; }
I expect it to come back "31+37+39+" but it's coming back only "37+" (if I test in on a page and use "print" instead of "return", I get exactly what I want)
that would be my woeful php obviously for which I apologise now.
I'm trying to adapt what Idan wrote but cannot get it to output multiple UIDs to insert as the user:uid argument.
I'm also getting Query Build Time in views of around 5050ms so I'm guessing my sql is massively inefficient too...I do need the join and the nested SELECTs though. I'm getting the UID of all the people who share the location taxonomy term on their profile with the logged in user, so it was never going to be easy!
by the way, in my query, I'm hardcoding the logged in user's UID (2) just for testing purposes.
Neil
On 20/11/2010 12:23, Idan Arbel wrote:
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 5:20 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node'&& is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlanneil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
It's not a sql query. db_fetch_object is like mysql_fetch_object(). It's just grabbing a row from a result resource, which is what you get from db_query($sql, ...), which you (hopefully) run only once.
On 11/24/2010 01:50 PM, Neil Coghlan wrote:
Ursula/Christopher,
Thanks for this help. I'll try it later when I'm at home. The only thing that concerns me is that views was showing 5000ms for that view to render so I suspect having such a complicated nested sql query in there just to supply the arg is going to be a killer.
Neil
On 24/11/2010 15:42, Christopher M. Jones wrote:
Or, to avoid a stray '+' at the end,
$members = array(); while( $member = db_fetch_object($result) ){ $members[] = $member->uid; }
return implode('+', $members);
On 11/24/2010 01:29 PM, Ursula Pieper wrote:
Neil,
If you use return inside the while loop like you do it, the loop will process the first return of the query (37), and return that value to the parent function. It basically will break the loop and won't go through it until the further entries are processed.
You need to concatenate the fetched values to a variable (for example), and return that variable once the loop is finished. while($member = db_fetch_object($result)){ $variable.= $member->uid . "+"; } return $variable;
If you want to retrieve the values from the database ordered, you need to add an "order by node.uid asc" to the SQL query.
Hope this helps, Ursula
On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlanneil@esl-lounge.com wrote:
this is where I'm up to
$result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM node WHERE node.uid IN (SELECT node.uid FROM node WHERE nid IN (SELECT nid FROM content_type_profile WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile JOIN node ON content_type_profile.nid = node.nid WHERE node.uid = 2 AND node.type = 'profile'))) AND node.uid != 2"); $member = db_fetch_object($result); while($member = db_fetch_object($result)){ return $member->uid . "+"; }
I expect it to come back "31+37+39+" but it's coming back only "37+" (if I test in on a page and use "print" instead of "return", I get exactly what I want)
that would be my woeful php obviously for which I apologise now.
I'm trying to adapt what Idan wrote but cannot get it to output multiple UIDs to insert as the user:uid argument.
I'm also getting Query Build Time in views of around 5050ms so I'm guessing my sql is massively inefficient too...I do need the join and the nested SELECTs though. I'm getting the UID of all the people who share the location taxonomy term on their profile with the logged in user, so it was never going to be easy!
by the way, in my query, I'm hardcoding the logged in user's UID (2) just for testing purposes.
Neil
On 20/11/2010 12:23, Idan Arbel wrote:
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 5:20 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node'&& is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlanneil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
Well each time that page is called, won't the view run? Each user accessing that view will be presented with a new set of results because their profile values will be different and that is what I'm basing the view around. When testing it yesterday, I was waiting 5 seconds for each page load. This is when it was only returning the single uid instead of the multiples that I'll hopefully be able to do now.
I did some testing yesterday by hardcoding the argument and the view rendered in 1-200ms so it is that db_query() that is responsible for the 5000ms.
On 24/11/2010 16:03, Christopher M. Jones wrote:
It's not a sql query. db_fetch_object is like mysql_fetch_object(). It's just grabbing a row from a result resource, which is what you get from db_query($sql, ...), which you (hopefully) run only once.
On 11/24/2010 01:50 PM, Neil Coghlan wrote:
Ursula/Christopher,
Thanks for this help. I'll try it later when I'm at home. The only thing that concerns me is that views was showing 5000ms for that view to render so I suspect having such a complicated nested sql query in there just to supply the arg is going to be a killer.
Neil
On 24/11/2010 15:42, Christopher M. Jones wrote:
Or, to avoid a stray '+' at the end,
$members = array(); while( $member = db_fetch_object($result) ){ $members[] = $member->uid; }
return implode('+', $members);
On 11/24/2010 01:29 PM, Ursula Pieper wrote:
Neil,
If you use return inside the while loop like you do it, the loop will process the first return of the query (37), and return that value to the parent function. It basically will break the loop and won't go through it until the further entries are processed.
You need to concatenate the fetched values to a variable (for example), and return that variable once the loop is finished. while($member = db_fetch_object($result)){ $variable.= $member->uid . "+"; } return $variable;
If you want to retrieve the values from the database ordered, you need to add an "order by node.uid asc" to the SQL query.
Hope this helps, Ursula
On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlanneil@esl-lounge.com wrote:
this is where I'm up to
$result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM node WHERE node.uid IN (SELECT node.uid FROM node WHERE nid IN (SELECT nid FROM content_type_profile WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile JOIN node ON content_type_profile.nid = node.nid WHERE node.uid = 2 AND node.type = 'profile'))) AND node.uid != 2"); $member = db_fetch_object($result); while($member = db_fetch_object($result)){ return $member->uid . "+"; }
I expect it to come back "31+37+39+" but it's coming back only "37+" (if I test in on a page and use "print" instead of "return", I get exactly what I want)
that would be my woeful php obviously for which I apologise now.
I'm trying to adapt what Idan wrote but cannot get it to output multiple UIDs to insert as the user:uid argument.
I'm also getting Query Build Time in views of around 5050ms so I'm guessing my sql is massively inefficient too...I do need the join and the nested SELECTs though. I'm getting the UID of all the people who share the location taxonomy term on their profile with the logged in user, so it was never going to be easy!
by the way, in my query, I'm hardcoding the logged in user's UID (2) just for testing purposes.
Neil
On 20/11/2010 12:23, Idan Arbel wrote:
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 5:20 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node'&& is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlanneil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
Yes, every time the page that builds and executes the view is accessed the view is built and executed. Once for every user that accesses the view. If your view is taking that long to execute then this is problem not with the snippet in question but with the views query, which may need to be optimized. You may need to add an index on some tables to help pull that query time down.
Or, if you're building and executing the view inside a loop then this would explain it, too. But that's not happening in the snippet below.
On 11/24/2010 02:37 PM, Neil Coghlan wrote:
Well each time that page is called, won't the view run? Each user accessing that view will be presented with a new set of results because their profile values will be different and that is what I'm basing the view around. When testing it yesterday, I was waiting 5 seconds for each page load. This is when it was only returning the single uid instead of the multiples that I'll hopefully be able to do now.
I did some testing yesterday by hardcoding the argument and the view rendered in 1-200ms so it is that db_query() that is responsible for the 5000ms.
On 24/11/2010 16:03, Christopher M. Jones wrote:
It's not a sql query. db_fetch_object is like mysql_fetch_object(). It's just grabbing a row from a result resource, which is what you get from db_query($sql, ...), which you (hopefully) run only once.
On 11/24/2010 01:50 PM, Neil Coghlan wrote:
Ursula/Christopher,
Thanks for this help. I'll try it later when I'm at home. The only thing that concerns me is that views was showing 5000ms for that view to render so I suspect having such a complicated nested sql query in there just to supply the arg is going to be a killer.
Neil
On 24/11/2010 15:42, Christopher M. Jones wrote:
Or, to avoid a stray '+' at the end,
$members = array(); while( $member = db_fetch_object($result) ){ $members[] = $member->uid; }
return implode('+', $members);
On 11/24/2010 01:29 PM, Ursula Pieper wrote:
Neil,
If you use return inside the while loop like you do it, the loop will process the first return of the query (37), and return that value to the parent function. It basically will break the loop and won't go through it until the further entries are processed.
You need to concatenate the fetched values to a variable (for example), and return that variable once the loop is finished. while($member = db_fetch_object($result)){ $variable.= $member->uid . "+"; } return $variable;
If you want to retrieve the values from the database ordered, you need to add an "order by node.uid asc" to the SQL query.
Hope this helps, Ursula
On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlanneil@esl-lounge.com wrote:
this is where I'm up to
$result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM node WHERE node.uid IN (SELECT node.uid FROM node WHERE nid IN (SELECT nid FROM content_type_profile WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile JOIN node ON content_type_profile.nid = node.nid WHERE node.uid = 2 AND node.type = 'profile'))) AND node.uid != 2"); $member = db_fetch_object($result); while($member = db_fetch_object($result)){ return $member->uid . "+"; }
I expect it to come back "31+37+39+" but it's coming back only "37+" (if I test in on a page and use "print" instead of "return", I get exactly what I want)
that would be my woeful php obviously for which I apologise now.
I'm trying to adapt what Idan wrote but cannot get it to output multiple UIDs to insert as the user:uid argument.
I'm also getting Query Build Time in views of around 5050ms so I'm guessing my sql is massively inefficient too...I do need the join and the nested SELECTs though. I'm getting the UID of all the people who share the location taxonomy term on their profile with the logged in user, so it was never going to be easy!
by the way, in my query, I'm hardcoding the logged in user's UID (2) just for testing purposes.
Neil
On 20/11/2010 12:23, Idan Arbel wrote:
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 5:20 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node'&& is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlanneil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
Your query looks overly complicated with a large number of sub-selects, which probably is the reason for the bad performance of you query.
Not knowing your table structure/etc, I would suggest reformulating the query to something like this:
Select n2.nid, n2.title, n2.uid from node n1, node n2, content_type_profile c, content_type_profile c1 where n1.type="profile" and c.nid=n1.nid and c1.nid=n2.nid and c.field_city_value=c1.field_city_value and n1.uid=2 and n2.uid<>2 ;
(this is just wild guesswork on my part, you probably need to change things to make it right).
If the reformulation still doesn't speed it up significantly, I would look at the indices, and would index the columns that are queried (fields that are after the "where"), especially for larger tables (but for good practice, also the smaller tables).
If a column is part of the primary key, it is automatically indexed, but the index is only useful if it is the first field in the primary key (or additional index).
To see the indices of your tables, execute (on the mysql prompt): show index from tablename; (e.g. "show index from node;").
Hope this helps, Ursula
On Wed, Nov 24, 2010 at 11:37 AM, Neil Coghlan neil@esl-lounge.com wrote:
Well each time that page is called, won't the view run? Each user accessing that view will be presented with a new set of results because their profile values will be different and that is what I'm basing the view around. When testing it yesterday, I was waiting 5 seconds for each page load. This is when it was only returning the single uid instead of the multiples that I'll hopefully be able to do now.
I did some testing yesterday by hardcoding the argument and the view rendered in 1-200ms so it is that db_query() that is responsible for the 5000ms.
On 24/11/2010 16:03, Christopher M. Jones wrote:
It's not a sql query. db_fetch_object is like mysql_fetch_object(). It's just grabbing a row from a result resource, which is what you get from db_query($sql, ...), which you (hopefully) run only once.
On 11/24/2010 01:50 PM, Neil Coghlan wrote:
Ursula/Christopher,
Thanks for this help. I'll try it later when I'm at home. The only thing that concerns me is that views was showing 5000ms for that view to render so I suspect having such a complicated nested sql query in there just to supply the arg is going to be a killer.
Neil
On 24/11/2010 15:42, Christopher M. Jones wrote:
Or, to avoid a stray '+' at the end,
$members = array(); while( $member = db_fetch_object($result) ){ $members[] = $member->uid; }
return implode('+', $members);
On 11/24/2010 01:29 PM, Ursula Pieper wrote:
Neil,
If you use return inside the while loop like you do it, the loop will process the first return of the query (37), and return that value to the parent function. It basically will break the loop and won't go through it until the further entries are processed.
You need to concatenate the fetched values to a variable (for example), and return that variable once the loop is finished. while($member = db_fetch_object($result)){ $variable.= $member->uid . "+"; } return $variable;
If you want to retrieve the values from the database ordered, you need to add an "order by node.uid asc" to the SQL query.
Hope this helps, Ursula
On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlanneil@esl-lounge.com wrote:
this is where I'm up to
$result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM node WHERE node.uid IN (SELECT node.uid FROM node WHERE nid IN (SELECT nid FROM content_type_profile WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile JOIN node ON content_type_profile.nid = node.nid WHERE node.uid = 2 AND node.type = 'profile'))) AND node.uid != 2"); $member = db_fetch_object($result); while($member = db_fetch_object($result)){ return $member->uid . "+"; }
I expect it to come back "31+37+39+" but it's coming back only "37+" (if I test in on a page and use "print" instead of "return", I get exactly what I want)
that would be my woeful php obviously for which I apologise now.
I'm trying to adapt what Idan wrote but cannot get it to output multiple UIDs to insert as the user:uid argument.
I'm also getting Query Build Time in views of around 5050ms so I'm guessing my sql is massively inefficient too...I do need the join and the nested SELECTs though. I'm getting the UID of all the people who share the location taxonomy term on their profile with the logged in user, so it was never going to be easy!
by the way, in my query, I'm hardcoding the logged in user's UID (2) just for testing purposes.
Neil
On 20/11/2010 12:23, Idan Arbel wrote:
You can see that I cycle through the id's and add them to $args[0] and return it. Similar to what you did, try changing the variable name to $args[0].
for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" }
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 5:20 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
hmm, can't see how that would apply in my case. Maybe it does, but can't see it.
bottom line: if an sql query returns 3 values, how do I get those 3 values into a Views argument via "provide default argument"?
On 20/11/2010 10:30, Idan Arbel wrote:
Take a look at this code I used for something similar:
if (arg(0) == 'node'&& is_numeric(arg(1))) { $node=node_load(arg(1)); $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the vocabulary id $terms = array_keys($term); $args[0] = $terms[0]; for($i=1; $i<count($terms); $i++) $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want and "And" action instead of "or" } return $args[0];
might help you out.
Idan
From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Neil Coghlan Sent: Saturday, November 20, 2010 3:25 PM To: support@drupal.org Subject: Re: [support] Passing multiple values into a single Views argument
Idan, I only just realised myself there was an "accept multiple arguments" checkbox....without that, I never would get it working!
so...now with that checked, I still need to get the UID's passed in x,y,z or x+y+z format.
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlanneil@esl-lounge.com wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view.
At the moment, the view is only taking the first one.
so, here is a simplified version of my php code:
global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid";
on my current db, this returns 3 UIDs...the view only takes the first one.
Thanks
Neil
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
Ursula, that query needed the tiniest tweak and takes 90ms to run as opposed to my 5000ms!! I think my query was literally the most inefficient way of doing it!
I never knew you could call a same table twice like that ("from node n1, node n2") and it makes it much easier to do. If you know of any beginner-level resource for learning more about that type of SELECT command in mysql, I'd appreciate you pointing me in that direction.
thanks again.
Neil
On 24/11/2010 18:41, Ursula Pieper wrote:
Your query looks overly complicated with a large number of sub-selects, which probably is the reason for the bad performance of you query.
Not knowing your table structure/etc, I would suggest reformulating the query to something like this:
Select n2.nid, n2.title, n2.uid from node n1, node n2, content_type_profile c, content_type_profile c1 where n1.type="profile" and c.nid=n1.nid and c1.nid=n2.nid and c.field_city_value=c1.field_city_value and n1.uid=2 and n2.uid<>2 ;
(this is just wild guesswork on my part, you probably need to change things to make it right).
If the reformulation still doesn't speed it up significantly, I would look at the indices, and would index the columns that are queried (fields that are after the "where"), especially for larger tables (but for good practice, also the smaller tables).
If a column is part of the primary key, it is automatically indexed, but the index is only useful if it is the first field in the primary key (or additional index).
To see the indices of your tables, execute (on the mysql prompt): show index from tablename; (e.g. "show index from node;").
Hope this helps, Ursula
On Wed, Nov 24, 2010 at 11:37 AM, Neil Coghlanneil@esl-lounge.com wrote:
Well each time that page is called, won't the view run? Each user accessing that view will be presented with a new set of results because their profile values will be different and that is what I'm basing the view around. When testing it yesterday, I was waiting 5 seconds for each page load. This is when it was only returning the single uid instead of the multiples that I'll hopefully be able to do now.
I did some testing yesterday by hardcoding the argument and the view rendered in 1-200ms so it is that db_query() that is responsible for the 5000ms.
On 24/11/2010 16:03, Christopher M. Jones wrote:
It's not a sql query. db_fetch_object is like mysql_fetch_object(). It's just grabbing a row from a result resource, which is what you get from db_query($sql, ...), which you (hopefully) run only once.
On 11/24/2010 01:50 PM, Neil Coghlan wrote:
Ursula/Christopher,
Thanks for this help. I'll try it later when I'm at home. The only thing that concerns me is that views was showing 5000ms for that view to render so I suspect having such a complicated nested sql query in there just to supply the arg is going to be a killer.
Neil
On 24/11/2010 15:42, Christopher M. Jones wrote:
Or, to avoid a stray '+' at the end,
$members = array(); while( $member = db_fetch_object($result) ){ $members[] = $member->uid; }
return implode('+', $members);
On 11/24/2010 01:29 PM, Ursula Pieper wrote:
Neil,
If you use return inside the while loop like you do it, the loop will process the first return of the query (37), and return that value to the parent function. It basically will break the loop and won't go through it until the further entries are processed.
You need to concatenate the fetched values to a variable (for example), and return that variable once the loop is finished. while($member = db_fetch_object($result)){ $variable.= $member->uid . "+"; } return $variable;
If you want to retrieve the values from the database ordered, you need to add an "order by node.uid asc" to the SQL query.
Hope this helps, Ursula
On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlanneil@esl-lounge.com wrote: > this is where I'm up to > > $result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM > node WHERE node.uid IN (SELECT node.uid FROM node > WHERE nid IN (SELECT nid FROM content_type_profile > WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile > JOIN node ON content_type_profile.nid = node.nid > WHERE node.uid = 2 AND node.type = 'profile'))) > AND node.uid != 2"); > $member = db_fetch_object($result); > while($member = db_fetch_object($result)){ > return $member->uid . "+"; > } > > I expect it to come back "31+37+39+" but it's coming back only "37+" (if I > test in on a page and use "print" instead of "return", I get exactly what I > want) > > that would be my woeful php obviously for which I apologise now. > > I'm trying to adapt what Idan wrote but cannot get it to output multiple > UIDs to insert as the user:uid argument. > > I'm also getting Query Build Time in views of around 5050ms so I'm guessing > my sql is massively inefficient too...I do need the join and the nested > SELECTs though. I'm getting the UID of all the people who share the location > taxonomy term on their profile with the logged in user, so it was never > going to be easy! > > by the way, in my query, I'm hardcoding the logged in user's UID (2) just > for testing purposes. > > Neil > > On 20/11/2010 12:23, Idan Arbel wrote: > > You can see that I cycle through the id's and add them to $args[0] and > return it. Similar to what you did, try changing the variable name to > $args[0]. > > for($i=1; $i<count($terms); $i++) > $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want > and "And" action instead of "or" > } > > From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On > Behalf Of Neil Coghlan > Sent: Saturday, November 20, 2010 5:20 PM > To: support@drupal.org > Subject: Re: [support] Passing multiple values into a single Views argument > > > > hmm, can't see how that would apply in my case. Maybe it does, but can't see > it. > > bottom line: if an sql query returns 3 values, how do I get those 3 values > into a Views argument via "provide default argument"? > > On 20/11/2010 10:30, Idan Arbel wrote: > > Take a look at this code I used for something similar: > > > > if (arg(0) == 'node'&& is_numeric(arg(1))) { > $node=node_load(arg(1)); > $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the > vocabulary id > $terms = array_keys($term); > $args[0] = $terms[0]; > for($i=1; $i<count($terms); $i++) > $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want > and "And" action instead of "or" > } > return $args[0]; > > > > might help you out. > > Idan > > > > From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On > Behalf Of Neil Coghlan > Sent: Saturday, November 20, 2010 3:25 PM > To: support@drupal.org > Subject: Re: [support] Passing multiple values into a single Views argument > > > > Idan, I only just realised myself there was an "accept multiple arguments" > checkbox....without that, I never would get it working! > > so...now with that checked, I still need to get the UID's passed in x,y,z or > x+y+z format. > > On 20/11/2010 10:21, Idan Arbel wrote: > > if you want to view to take them into account as using "AND" then return > them like so: 12+32+34, if you want it to take them into account as or > return them as so 12,32,34. > > > > don't forget to check to box in the argument settings area to accept > multiple arguments > > On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlanneil@esl-lounge.com wrote: > > I have a view where I have selected User:uid as an argument and I am > using a sql query in the "Provide Default Argument" part. The problem > is, usually, the sql query is passing muliple UIDs back. How would I > pass all of them into the argument to be used by the view. > > At the moment, the view is only taking the first one. > > so, here is a simplified version of my php code: > > global $user; > $result = db_query("SELECT node.uid FROM {node} > WHERE node.type = 'profile'"); > $member = db_fetch_object($result); > return "$member->uid"; > > on my current db, this returns 3 UIDs...the view only takes the first one. > > Thanks > > Neil > > > -- > [ Drupal support list | http://lists.drupal.org/ ] > > > > -- > [ Drupal support list | http://lists.drupal.org/ ] >
-- [ Drupal support list | http://lists.drupal.org/ ]
Neil,glad it helped. About a resource about learning how to write efficient SQL: Any standard textbook should be good for this, such as the MySQL book from Paul DuBois. Best of luck, Ursula
On Wed, Nov 24, 2010 at 4:34 PM, Neil Coghlan neil@esl-lounge.com wrote:
Ursula, that query needed the tiniest tweak and takes 90ms to run as opposed to my 5000ms!! I think my query was literally the most inefficient way of doing it!
I never knew you could call a same table twice like that ("from node n1, node n2") and it makes it much easier to do. If you know of any beginner-level resource for learning more about that type of SELECT command in mysql, I'd appreciate you pointing me in that direction.
thanks again.
Neil
On 24/11/2010 18:41, Ursula Pieper wrote:
Your query looks overly complicated with a large number of sub-selects, which probably is the reason for the bad performance of you query.
Not knowing your table structure/etc, I would suggest reformulating the query to something like this:
Select n2.nid, n2.title, n2.uid from node n1, node n2, content_type_profile c, content_type_profile c1 where n1.type="profile" and c.nid=n1.nid and c1.nid=n2.nid and c.field_city_value=c1.field_city_value and n1.uid=2 and n2.uid<>2 ;
(this is just wild guesswork on my part, you probably need to change things to make it right).
If the reformulation still doesn't speed it up significantly, I would look at the indices, and would index the columns that are queried (fields that are after the "where"), especially for larger tables (but for good practice, also the smaller tables).
If a column is part of the primary key, it is automatically indexed, but the index is only useful if it is the first field in the primary key (or additional index).
To see the indices of your tables, execute (on the mysql prompt): show index from tablename; (e.g. "show index from node;").
Hope this helps, Ursula
On Wed, Nov 24, 2010 at 11:37 AM, Neil Coghlanneil@esl-lounge.com wrote:
Well each time that page is called, won't the view run? Each user accessing that view will be presented with a new set of results because their profile values will be different and that is what I'm basing the view around. When testing it yesterday, I was waiting 5 seconds for each page load. This is when it was only returning the single uid instead of the multiples that I'll hopefully be able to do now.
I did some testing yesterday by hardcoding the argument and the view rendered in 1-200ms so it is that db_query() that is responsible for the 5000ms.
On 24/11/2010 16:03, Christopher M. Jones wrote:
It's not a sql query. db_fetch_object is like mysql_fetch_object(). It's just grabbing a row from a result resource, which is what you get from db_query($sql, ...), which you (hopefully) run only once.
On 11/24/2010 01:50 PM, Neil Coghlan wrote:
Ursula/Christopher,
Thanks for this help. I'll try it later when I'm at home. The only thing that concerns me is that views was showing 5000ms for that view to render so I suspect having such a complicated nested sql query in there just to supply the arg is going to be a killer.
Neil
On 24/11/2010 15:42, Christopher M. Jones wrote:
Or, to avoid a stray '+' at the end,
$members = array(); while( $member = db_fetch_object($result) ){ $members[] = $member->uid; }
return implode('+', $members);
On 11/24/2010 01:29 PM, Ursula Pieper wrote: > Neil, > > If you use return inside the while loop like you do it, the loop will > process the first return of the query (37), and return that value to > the parent function. It basically will break the loop and won't go > through it until the further entries are processed. > > You need to concatenate the fetched values to a variable (for > example), and return that variable once the loop is finished. > while($member = db_fetch_object($result)){ > $variable.= $member->uid . "+"; > } > return $variable; > > If you want to retrieve the values from the database ordered, you need > to add an "order by node.uid asc" to the SQL query. > > Hope this helps, Ursula > > On Tue, Nov 23, 2010 at 7:10 AM, Neil Coghlanneil@esl-lounge.com wrote: >> this is where I'm up to >> >> $result = db_query("SELECT node.nid, node.title, node.type, node.uid FROM >> node WHERE node.uid IN (SELECT node.uid FROM node >> WHERE nid IN (SELECT nid FROM content_type_profile >> WHERE field_city_value IN (SELECT field_city_value FROM content_type_profile >> JOIN node ON content_type_profile.nid = node.nid >> WHERE node.uid = 2 AND node.type = 'profile'))) >> AND node.uid != 2"); >> $member = db_fetch_object($result); >> while($member = db_fetch_object($result)){ >> return $member->uid . "+"; >> } >> >> I expect it to come back "31+37+39+" but it's coming back only "37+" (if I >> test in on a page and use "print" instead of "return", I get exactly what I >> want) >> >> that would be my woeful php obviously for which I apologise now. >> >> I'm trying to adapt what Idan wrote but cannot get it to output multiple >> UIDs to insert as the user:uid argument. >> >> I'm also getting Query Build Time in views of around 5050ms so I'm guessing >> my sql is massively inefficient too...I do need the join and the nested >> SELECTs though. I'm getting the UID of all the people who share the location >> taxonomy term on their profile with the logged in user, so it was never >> going to be easy! >> >> by the way, in my query, I'm hardcoding the logged in user's UID (2) just >> for testing purposes. >> >> Neil >> >> On 20/11/2010 12:23, Idan Arbel wrote: >> >> You can see that I cycle through the id's and add them to $args[0] and >> return it. Similar to what you did, try changing the variable name to >> $args[0]. >> >> for($i=1; $i<count($terms); $i++) >> $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want >> and "And" action instead of "or" >> } >> >> From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On >> Behalf Of Neil Coghlan >> Sent: Saturday, November 20, 2010 5:20 PM >> To: support@drupal.org >> Subject: Re: [support] Passing multiple values into a single Views argument >> >> >> >> hmm, can't see how that would apply in my case. Maybe it does, but can't see >> it. >> >> bottom line: if an sql query returns 3 values, how do I get those 3 values >> into a Views argument via "provide default argument"? >> >> On 20/11/2010 10:30, Idan Arbel wrote: >> >> Take a look at this code I used for something similar: >> >> >> >> if (arg(0) == 'node'&& is_numeric(arg(1))) { >> $node=node_load(arg(1)); >> $term = taxonomy_node_get_terms_by_vocabulary($node, 4); // 4 being the >> vocabulary id >> $terms = array_keys($term); >> $args[0] = $terms[0]; >> for($i=1; $i<count($terms); $i++) >> $args[0] = $args[0] ."+" .$terms[$i]; // replace the + with , if you want >> and "And" action instead of "or" >> } >> return $args[0]; >> >> >> >> might help you out. >> >> Idan >> >> >> >> From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On >> Behalf Of Neil Coghlan >> Sent: Saturday, November 20, 2010 3:25 PM >> To: support@drupal.org >> Subject: Re: [support] Passing multiple values into a single Views argument >> >> >> >> Idan, I only just realised myself there was an "accept multiple arguments" >> checkbox....without that, I never would get it working! >> >> so...now with that checked, I still need to get the UID's passed in x,y,z or >> x+y+z format. >> >> On 20/11/2010 10:21, Idan Arbel wrote: >> >> if you want to view to take them into account as using "AND" then return >> them like so: 12+32+34, if you want it to take them into account as or >> return them as so 12,32,34. >> >> >> >> don't forget to check to box in the argument settings area to accept >> multiple arguments >> >> On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlanneil@esl-lounge.com wrote: >> >> I have a view where I have selected User:uid as an argument and I am >> using a sql query in the "Provide Default Argument" part. The problem >> is, usually, the sql query is passing muliple UIDs back. How would I >> pass all of them into the argument to be used by the view. >> >> At the moment, the view is only taking the first one. >> >> so, here is a simplified version of my php code: >> >> global $user; >> $result = db_query("SELECT node.uid FROM {node} >> WHERE node.type = 'profile'"); >> $member = db_fetch_object($result); >> return "$member->uid"; >> >> on my current db, this returns 3 UIDs...the view only takes the first one. >> >> Thanks >> >> Neil >> >> >> -- >> [ Drupal support list | http://lists.drupal.org/ ] >> >> >> >> -- >> [ Drupal support list | http://lists.drupal.org/ ] >>
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
I'm close I think to passing the multiple arguments.
On a test page, this sql embedded works
<?php global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid"; while($member = db_fetch_object($result)){ print "$member->uid"; print ","; } ?>
I end up with "37, 31, 2"
But I need to use "return" in the argument php code
if I use
while($member = db_fetch_object($result)){ return "$member->uid"; }
at the end, still only the first one is passed, not multiple values.
anyone give me a pointer?
On 20/11/2010 10:21, Idan Arbel wrote:
if you want to view to take them into account as using "AND" then return them like so: 12+32+34, if you want it to take them into account as or return them as so 12,32,34.
don't forget to check to box in the argument settings area to accept multiple arguments
On Sat, Nov 20, 2010 at 3:14 PM, Neil Coghlan <neil@esl-lounge.com mailto:neil@esl-lounge.com> wrote:
I have a view where I have selected User:uid as an argument and I am using a sql query in the "Provide Default Argument" part. The problem is, usually, the sql query is passing muliple UIDs back. How would I pass all of them into the argument to be used by the view. At the moment, the view is only taking the first one. so, here is a simplified version of my php code: global $user; $result = db_query("SELECT node.uid FROM {node} WHERE node.type = 'profile'"); $member = db_fetch_object($result); return "$member->uid"; on my current db, this returns 3 UIDs...the view only takes the first one. Thanks Neil -- [ Drupal support list | http://lists.drupal.org/ ]
-
Christopher M. Jones -
Idan Arbel -
Neil Coghlan -
Ursula Pieper