[support] Passing multiple values into a single Views argument

Thu Nov 25 00:34:48 UTC 2010

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 Coghlan<neil at 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 Coghlan<neil at 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 at drupal.org [mailto:support-bounces at drupal.org] On
>>>>>>> Behalf Of Neil Coghlan
>>>>>>> Sent: Saturday, November 20, 2010 5:20 PM
>>>>>>> To: support at 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 at drupal.org [mailto:support-bounces at drupal.org] On
>>>>>>> Behalf Of Neil Coghlan
>>>>>>> Sent: Saturday, November 20, 2010 3:25 PM
>>>>>>> To: support at 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 at 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/ ]
>>