[support] Passing multiple values into a single Views argument
Ursula Pieper
dramamezzo at gmail.com
Sun Nov 28 00:50:59 UTC 2010
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 at 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 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/ ]
>>>
> --
> [ Drupal support list | http://lists.drupal.org/ ]
>
More information about the support
mailing list