SP2010 Joins

Dec 6, 2011 at 8:26 PM

I was wondering if it is possible to take advantage of the <Joins> and <ProjectedFields> CAML functionality using SPServices with SharePoint 2010?  

Coordinator
Dec 6, 2011 at 8:39 PM

What happens when you try it?

M.

Dec 6, 2011 at 8:45 PM

Well, I added some code to 0.7.0 to allow for a <Joins> and <ProjectedFields> node (they have to be declared apart from the <Query>) in GetListItems and I'm not getting any different response back than if I'd not sent those tags with the message at all.  As far as I can tell I'm following the guidelines specified on MSDN (http://msdn.microsoft.com/en-us/library/ee539975(office.14).aspx).  I include the projected field name in the <ViewFields> section, as well.  

Has anyone else tried this?

Coordinator
Dec 6, 2011 at 8:50 PM

Can you post your code? It's hard to understand exactly what you're doing.

M.

Dec 6, 2011 at 9:07 PM

I have a list called TestList that contains a Lookup column (called Lookup1, matched on ID) to another list.  The lookup list has a text column called Description.  What I'm trying to do is use a join in my query of the TestList  to display the lookup list's Description that belongs to the records that are matched.  

As far as I can tell, there's nothing in SPServices that can handle joins yet, so here's the raw soap call to the lists.asmx service:

var msg =
                "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" +
                    "<soap:Body>" +
                        "<GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" +
                            "<listName>TestList</listName>" +
                            "<ViewFields>" +
                                "<FieldRef Name='LookupDesc'/>" +
                            "</ViewFields>" +
                            "<rowLimit>100</rowLimit>" +
                            "<Joins>" +
                                "<Join Type='LEFT' ListAlias='test'>" +
                                    "<Eq>" +
                                        "<FieldRef Name='Lookup1' RefType='Id'/>" +
                                        "<FieldRef List='test' Name='ID'/>" +
                                    "</Eq>" +
                                "</Join>" +
                            "</Joins>" +
                            "<ProjectedFields>" +
                                "<Field Name='LookupDesc' Type='Text' ShowField='Description' List='test'/>" +
                            "</ProjectedFields>" +
                        "</GetListItems>" +
                    "</soap:Body>" +
                "</soap:Envelope>";

            $.ajax({
                url: mySiteUrl + "/_vti_bin/Lists.asmx",
                async: false,
                type: "POST",
                data: msg,
                dataType: "xml",
                contentType: "text/xml;charset='utf-8'",
                complete: function (data, status) {
                    /* Look at my data */                    
                }
            });
Dec 9, 2011 at 6:11 PM

Has anyone tried this against the Lists.asmx service?  The code I posted above does not join, yet I can't seem to find an error in my syntax.  Has anyone else had success and if so, could you post an example?

Coordinator
Dec 9, 2011 at 6:58 PM

Have you seen any documentation that joins should work with the Lists Web Service? I can't recall having seen anything. If you have and can point me to it, I'd be interested.

M.

Dec 12, 2011 at 3:30 PM

I haven't seen the web service called out as being specifically supported.  I suppose I made the assumption that SP2010 would extend the feature into the services.  This MSDN page (http://msdn.microsoft.com/en-us/library/ee539975(office.14).aspx) describes the syntax and states that it works with both the view and query CAML syntax.  Perhaps they didn't include it, though.

Coordinator
Dec 12, 2011 at 3:34 PM

The canonical reference is the SharePoint SDK. Here's the page for GetListItems: http://msdn.microsoft.com/en-us/library/websvclists.lists.getlistitems.aspx I've not seen anything there that talks about Joins or Projected Fields. The Web Services are really different beasties, and I'm sure are managed by totally different groups at Microsoft. There was very little change in the existing Web Services from 2007 to 2010.

M.

Dec 12, 2011 at 8:55 PM

This is a slightly different approach, but may achieve what you want. It uses SharePoint Designer rather than javascript, so may be a non-starter for you.

http://blah.winsmarts.com/2007-10-Performing_joins_between_SharePoint_lists.aspx

HTH

From: goomoopunch [email removed]
Sent: Monday, December 12, 2011 4:31 PM
To: steve.eagleson@sky.com
Subject: Re: SP2010 Joins [SPServices:282044]

From: goomoopunch

I haven't seen the web service called out as being specifically supported. I suppose I made the assumption that SP2010 would extend the feature into the services. This MSDN page (http://msdn.microsoft.com/en-us/library/ee539975(office.14).aspx) describes the syntax and states that it works with both the view and query CAML syntax. Perhaps they didn't include it, though.

Dec 13, 2011 at 1:19 AM

@EagleUK

That's not a real join.  The XSLT under the hood is doing the filtering.  The new <Join> node in CAML does provide a query that will do a real join and that's what goomoopunch is trying to do.  I'm 

pretty interested in knowing how to do this as well.  I'll have to toy around with it.  I wonder if an Implicit Join would help you out goomoopunch.

 

Cheers,

Matt

Apr 8, 2013 at 12:23 PM
Hi Matt,

I am trying to achieve the same thing i.e. apply "joins" on CAML queries using SPservices or the Sharepoint webservices . Please let me know if thats possible in any case .
Apr 8, 2013 at 1:24 PM
@ReenaN,
Some others have reported <Join> not working with the web services. You should look at using JSOM instead.

Cheers,
Matthew