Aggregate results from two GetListItems functions

Jul 19, 2011 at 7:24 PM

Project: Use SPServices GetListItems to call data from two lists and combine the results. The two lists are related to each other through a lookup column. 

Why not just use a DFWP? Well, this project displays the data in a tabs interface - specifically a set of horizontal tabs across the top with a set of vertical tabs nested in each top tab amounting to about 80 tab panes. It's a slice 'n dice, where each pane loads a subset of the combined data. When a DFWP is used in each tab, ALL DFWPs load and query the lists at the same time when each user opens the page, resulting in slow page times and spikes in server CPU. 

Instead, this project uses Flowplayer's jQuery.tools AJAX tabs to load each tab pane as a separate HTM page, where each HTM page calls SPServices to get the list items. It's fast.

Problem: I can query either of the two lists and display/format the data just fine separately, but I'm unable to understand how to "push those results into an array and then process the superset." Any suggestions or examples to get me going in the right direction?

Coordinator
Jul 19, 2011 at 9:49 PM

What you'll want to do is effectively build a "join" between the results from the two lists. Because they have a value in common, you can iterate through one set of results and grab the matching results form the other set.

M.

Coordinator
Jul 19, 2011 at 11:08 PM

I just reread your post. If each tab is a separate HTM page, wouldn't you sinmply filter to only the items you want in each page's code?

M.

Jul 20, 2011 at 1:16 AM

The CAMLQuery on each HTM page filters a multi-select Choice column where 'Contains' text, eg:

var queryOne = '<Query>'
	+ '<OrderBy><FieldRef Name="Title" /></OrderBy>'
	+ '<Where><Contains><FieldRef Name="ORServiceArea" /><Value Type="Text">Portland</Value></Contains></Where>'
	+ '</Query>';

Works great! The rows returned on the pages still need to join photos, contacts and phone numbers in one list with the address info in another list.

1. Do you mean using the join() method? 

2. Is the SPServices function for each list nested in the 'join' and NOT nested one within the other?

3. I'm happily outputting the $(xData.responseXML).find("[nodeName='z:row']").each(function() directly to HTML for each list, but I have no experience outputting to arrays that can be joined together. Any pointers on how to do this?

4. Because they do share an ID in common, would a merge also work - along the lines of http://stackoverflow.com/questions/6059084/merging-extend-javascript-object-arrays-based-on-join-of-a-key-property-in-each, to wit:

function merge(a1,a2) {
	var i = -1;
	while ((i = i+1)<a1.length)  {
		for (var l in a2[i]) {
			if (!(l in a1[i] )) {
				a1[i][l] = a2[i][l];
			}
		}
	}
	return a1;
} 

I would love to lay out the working code here when I'm done. Should be useful. The tabs web part that results sure will be...

Coordinator
Jul 21, 2011 at 1:47 PM

I used quotes around "join" because that's something you'll need to work out. There are multiple methods you can use in your script to "join" the items fro the two list, but it's hard to tell from 30,000 feet exactly what the best approach will be for you.

M.

Jul 21, 2011 at 6:28 PM

Thanks for responding Marc. Here's the view from the ground... I seem to be having trouble with where to put the "join" code so that it applies to each row from each list as it outputs. This code at least displays the contents of each list - limited to CAMLRowLimit=50 only for testing.

<!DOCTYPE HTML>
<html>
	<head>
		<link href="/or/scripts/css/tabs.css">
		<script src="/or/scripts/jquery-1.6.2.min.js" language="javascript" type="text/javascript"></script>
		<script src="/or/scripts/jquery.SPServices-0.6.2.min.js" language="javascript" type="text/javascript"></script>
		<script type="text/javascript">
		
		$(document).ready(function() {
			var output1 = '';
			var output2 = '';
					
			//The Main List - output all the Clinics with their lookups to the Facilities list
			$().SPServices({
				operation: 'GetListItems',
				webURL: '/or',
				listName: 'Clinics',
				CAMLQuery: '',
				CAMLViewFields: '<ViewFields><FieldRef Name="Title" /><FieldRef Name="FacilityID" /></ViewFields>',
				CAMLRowLimit: 50,
				completefunc: function (xData, Status) {
					$(xData.responseXML).find("[nodeName='z:row']").each(function() {
						var tmpClinicTitle = $(this).attr("ows_Title");
						//split the Facility lookup value into its ID and Title elements
						var tmpFacilityIDLookup = $(this).attr("ows_FacilityID").split(";#").shift();
						var tmpFacilityTitleLookup = $(this).attr("ows_FacilityID").split(";#").pop();
						
						output1 += "<tr><td>" + tmpClinicTitle + "</td><td width='20px'></td><td>" + tmpFacilityIDLookup + "</td><td width='20px'></td><td>Lookup - " + tmpFacilityTitleLookup + "</td></tr>";

					});
					$("#Clinics").append(output1);
		  		}
			});

			//The Lookup List - output all Facilities
			$().SPServices({
				operation: 'GetListItems',
				webURL: '/or',
				listName: 'Facilities',
				CAMLQuery: '<Query><OrderBy><FieldRef Name="ID" /></OrderBy></Query>',
				CAMLViewFields: '<ViewFields><FieldRef Name="ID" /><FieldRef Name="Title" /></ViewFields>',
				CAMLRowLimit: 60,
				completefunc: function (xData, Status) {
					$(xData.responseXML).find("[nodeName='z:row']").each(function() {
						var tmpFacilityID = $(this).attr("ows_ID");
						var tmpFacilityTitle = $(this).attr("ows_Title");
					
						output2 += "<tr><td>" + tmpFacilityID + "</td><td width='20px'></td><td>" + tmpFacilityTitle + "</td></tr>";
					});
					$("#Clinics").append(output2);
				}
			});
 		});
		</script>
	</head>
	<body>
		<table id="Clinics" cellpadding="0" cellspacing="0" border="0"></table>
	</body>
</html>

Jul 22, 2011 at 8:01 AM

Looks like one "join" method that will work is simply nesting GetListItems. Not sure it's the fastest method, but I'll post the details tomorrow for comment.

Coordinator
Jul 22, 2011 at 1:06 PM

Right. Nesting your GetListItems calls is the most straightforward way, but if your're building as many tabs as individual pages as you describe, it may be really inefficient. However, as a starting point, it probably makes sense. Then you can see where the performance issues are and start thinking about a caching scheme.

M.

Jul 22, 2011 at 1:09 PM

It's not clear whether or not you are on 2010.  If so, you can do a real join in CAML.  Look over this MSDN article for details.

Cheers,

Matt

Jul 22, 2011 at 5:03 PM

It's 2007, good point Matt. I'm still unclear how SPServices supports a 2010 CAML join yet. That thread will be relevant in the project after this one, and I will pursue it then. 

The nested GetListItem sends a web services query with each xData.responseXML cycle, right? If the goal is to reduce the number of queries against the list to reduce server CPU load, then it would make more sense to get each list only once, store their data in object arrays, merge those arrays into a new object array where the IDs are equal, and then parse the array elements into HTML strings that can be sent to the #table. I'm unable to get the arrays out of their respective SPServices scopes to where they can be merged at present.

Anyhow, here's the working prototype:

 

<!DOCTYPE HTML>
<html>
	<head>
		<link href="/or/scripts/css/tabs.css">
		<script src="/or/scripts/jquery-1.6.2.min.js" language="javascript" type="text/javascript"></script>
		<script src="/or/scripts/jquery.SPServices-0.6.2.min.js" language="javascript" type="text/javascript"></script>
		<script type="text/javascript">
		
		$(document).ready(function() {
			// the Main list - output all Clinics
			$().SPServices({
				operation: 'GetListItems',
				webURL: '/or',
				listName: 'Clinics',
				CAMLQuery: '',  // add OrderBy and Where later
				CAMLViewFields: '<ViewFields>'
					+ '<FieldRef Name="Title" />'
					+ '<FieldRef Name="FacilityID" />'
					+ '<FieldRef Name="Main_x0020_Line" />'
					+ '</ViewFields>',
				CAMLRowLimit: 20, //limit to 0 after testing as results will be under 100 max.
				completefunc: function (xData, Status) {
					$(xData.responseXML).find("[nodeName='z:row']").each(function() {
						var tmpClinicTitle = $(this).attr("ows_Title");
						// the FacilityID is a lookup, so split off the ID element to the left of the string
						var tmpFacilityIDLookup = $(this).attr("ows_FacilityID").split(";#").shift();
						var tmpMain_x0020_Line = $(this).attr("ows_Main_x0020_Line");
						// tmpOtherClinicListStuff...
						
						// the Lookup list - output only the Facilities with the same ID
						$().SPServices({
							operation: 'GetListItems',
							webURL: '/or',
							listName: 'Facilities',
							CAMLQuery: '<Query><Where><Eq>'  // use the FacilityID from Clinics list for the Query filter
								+ '<FieldRef Name="ID" /><Value Type="Text">' + tmpFacilityIDLookup  + '</Value>'
								+ '</Eq></Where></Query>',
							CAMLViewFields: '<ViewFields>'
								+ '<FieldRef Name="ID" />'
								+ '<FieldRef Name="Address1" />'
								+ '</ViewFields>',
							CAMLRowLimit: 20,
							completefunc: 
								function (xData, Status) {
									var outputHTML = '';
									
									$(xData.responseXML).find("[nodeName='z:row']").each(function() {
										var tmpFacilityID = $(this).attr("ows_ID");
										tmpFacilityTitle = $(this).attr("ows_Title");
										tmpAddress1 = $(this).attr("ows_Address1");
										// tmpOtherFacilityListCityStateZipStuff...
										
										outputHTML =  "<tr>";
										outputHTML += "<td>" + tmpClinicTitle + "</td>"; 	 			//values from the Clinics list
										outputHTML += "<td>" + tmpFacilityTitle + "</td><td>" + tmpAddress1 + "</td>";  //values from the Facilities list
										outputHTML += "<td>" + tmpMain_x0020_Line + "</td>";     			//values from the Clinics list again
										outputHTML += "</tr>";
									});
								
								$("#Clinics").append(outputHTML);
							}
						});
					});
		  		}
			});
		});
		</script>
	</head>
	<body>
		<table id="Clinics" cellpadding="0" cellspacing="0" border="0"></table>
	</body>
</html>

 

 

Coordinator
Jul 22, 2011 at 5:22 PM

derungsm:

It looks like you have the basic structure right, but you're right in that you'll be doing multiple calls to GetListItems to get items from Facilities.

SPServices won't help you at all with joining; you'll be doing that in your script. Remember that xData.responseXML is an XML object, so you can save it to a var and parse it at your leisure.

You can see examples of this in SPDisplayRelatedInfo in SPServices. I save off the list XML from GetList and parse it later. (Best to look at v0.6.3ALPHA6, where I've made some efficiency changes around exactly this.)

M.

Jul 25, 2011 at 10:00 PM

Marc:

Do you mean something along the lines of:

$().SPServices({
	... lots of parameters for Clinics list
	completefunc: getClinicsXML
});

function getClinicsXML(xData, Status) {
	var clinicsXML = (xData.responseXML.xml);
	
	$().SPServices({
 		... lots of parameters for Facilities list
 		... could even restrict results to the Clinics list query
		completefunc: getFacilitesXML
	});

	function getFacilitesXML(xData, Status) {
		var facilitiesXML = (xData.responseXML.xml);
		
		... function(s) that parse(s) XML of both vars into object arrays
		... merges arrays
		... sort array results and convert to HTML
		... send results to ID#				
	}		
}

It queries each list for saves the XML into variables that get then get parsed, merged and formatted...

Coordinator
Jul 26, 2011 at 1:57 PM
Edited Jul 26, 2011 at 10:23 PM

Something like this probably makes sense. You could also simply make a call to GetListItems for each of the lists involved, getting all of the items. (I have no idea if the number of items in your lists makes this feasible or not.) Then you operate on the XML the same way you would if you were doing so inside the completefuncs.

M.

Jul 26, 2011 at 3:57 PM

Wow, what a coincidence - I JUST finished building a script which does something remarkably similar.

My requirement was to build a report engine that could accept as few as 1 and as many as 25 parameters.  I set up a number of input boxes/ddls/checkboxes on an aspx page, and allow the user to fill in the parameters.

The difficult thing was trying to come up with a solution which would ignore null inputs and in building the caml from only the parameters which were entered.  The <AND>ing was a nightmare because of the syntax being different for those first two parameters, which may or may not exist.  I ended up hardcoding a call which pulls all items with a Created > 1/1/1990 and an ID > 0, which would give me the full data set but allow me to just add <AND>s on in a singular fashion.  Right now, this seems to have made no significant speed impact.  The multiple web service calls are the real performance killer.  Anyway, once I figured out the caml, then on to the join.

I also ended up doing multiple nested calls to the other lists - I have 2 lists which are joined on a parent ID, and the data from those other 2 fields needs to be displayed as part of a single record.  I ended up outputting the main list data "Requests" to a table, then nesting and looping through the secondary ("Items"), then the tertiary ("Statuses") lists to pull the extra fields.  In the case where there are matches on an Items field and/or Statuses, but not on the parent, I remove those rows prior to displaying the entire table.  I wish I could think of a better way to do that.

Anyway, I output a TD for each of the secondary and tertiary fields as a "key" field, in the form "Itemkey123" / "Statuskey123" where the number is the parent ID from the Request list.  this allows me to pinpoint that TD as a 'hook' in a jquery call, and I append the data from those two lists there, so everything's in one big line.  It's remarkably fast when there are parameters on the parent list (as most of the time queries will be).  It's remarkably SLOW where there are only fields from the Items or Statuses lists, because (I realized the other day) it's actually calling all the items in the parent Request list first, matching only on returned elements from the other two lists, and having to remove them. Takes forever! 

I want to go back now to rewrite the logic so that if there is no parent Request parameter entered, the GetListItems will call the secondary or tertiary list first, and match the parent items backwards. 

 

Oh! one huge important item when doing this:  INDEX your key fields and any others that you're using as a filter in your caml.  Saves loads of time -

 

If anyone out there has insight into putting the first GetListItems output into a javascript object all at once, then doing a single call for only the matching items from the other two lists, i think I could get this thing going much more quickly. 

Jul 26, 2011 at 5:07 PM

This I think would be a great example of when it just might be feasible to use an XML to JSON conversion.

It is sad that I can see this in my head working, but in general if you do the conversion you have a ready made way of matching elements together. So if you convert each result set then you can do anything you want with the resulting javascript.

Dan

Jul 26, 2011 at 6:15 PM

I've been looking into XQuery as well, but there doesn't seem to be a good way to implement it easily on 2007/2010.  There is apparently a javascript library but it's very early in its maturity:  http://www.xqib.org?p=15

plus I'm not sure if it would be any faster than what's happening now anyway.  I've got to imagine the CAML join in 2010 is much faster than all this?

 





Coordinator
Jul 26, 2011 at 10:26 PM

Once you've stored the XML results into a var, you can iterate over it later in your code. While JSON may seem preferable, it won't necessarily work any better than using .each() over the XML results.

It may seem like I'm being a stick in the mud about the XML vs. JSON thing, but I'm not. In general, since the Web Services returns XML, it's best to work with the XML. Any translation to JSON will incur overhead, so you need to have a clear benefit in mind to make it worthwhile.

M.

Jul 27, 2011 at 1:34 AM

No worries Marc about the JSON. I was recently working with another plugin that I was writing and have just recently discovered JSON. Though I did have to use a .each on the xml to convert it to JSON, once I had it stored, it just seemed much easier to work with for me. If I were doing something like this, I might use it because I personally find it easier to read if that makes sense!

Coordinator
Dec 30, 2011 at 7:30 PM

Please see my blog post about including an XML to JSON conversion capability in SPServices and let me know what you think.

M.