在 CAML 查询中插入 WHERE 子句 - 使用 SharePoint、SPServices、JavaScript 和 jQuery
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17324989/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert a WHERE clause in a CAML query - using SharePoint, SPServices, JavaScript, and jQuery
提问by WCS
I am trying to insert a WHERE clause into my CAML query to filter a date column for NULL (I want to return all rows for which there is no date in Assign Date the column).
我正在尝试将 WHERE 子句插入到我的 CAML 查询中以过滤日期列的 NULL（我想返回所有在“Assign Date the column”中没有日期的行）。
This is for a SharePoint List. The code is using the SPServices, jQuery, and JavaScript
这是用于 SharePoint 列表。代码使用 SPServices、jQuery 和 JavaScript
I am having quite a time pulling this off - I have very little experience doing this type of integration and less still in using CAML.
我有相当长的时间来解决这个问题 - 我在做这种类型的集成方面的经验很少，而且在使用 CAML 方面的经验也很少。
This is the CAML query segment of the code:
这是代码的 CAML 查询段：
function loadPrioritizedList() {
$("#tasksUL").empty();
$().SPServices({
operation: "GetListItems",
webURL: myURL,
listName: targetListName,
CAMLViewFields: "<ViewFields><FieldRef Name='Priority_x0020_Number' /><FieldRef Name='Edit_x0020_Link' /><FieldRef Name='Priority' /><FieldRef Name='Top_x0020_Item_x003f_' /><FieldRef Name='Purpose' /><FieldRef Name='Item_x002d_Task_x0020_Order' /><FieldRef Name='Mode' /><FieldRef Name='Work_x0020_Status' /><FieldRef Name='DueDate' /><FieldRef Name='Task_x0020_Type' /><FieldRef Name='DAK_x0020_Date' /><FieldRef Name='DAK_x0020_No' /><FieldRef Name='AssignedTo' /><FieldRef Name='Money_x0020_Estimate' /><FieldRef Name='ItemStatus' /><FieldRef Name='Assign_x0020_Date' /></ViewFields>",
CAMLQuery: '<Query>' +
'<OrderBy>' +
'<FieldRef Name="Priority_x0020_Number" />' +
'</OrderBy>' +
'</Query>',
CAMLRowLimit: listrowlimit,
completefunc: function (xData, Status) {
$(xData.responseXML).SPFilterNode("z:row").each(function() {
var tdHtml = "<tr class='sortable_row' id=" + $(this).attr("ows_ID") + ">";
tdHtml = tdHtml + "<td style=\"width:60px;\">" + PriorityFormat($(this).attr("ows_Priority_x0020_Number")); + "</td>";
tdHtml = tdHtml + '<td style=\"width:49px;\"><a href=\"'+($(this).attr("ows_Edit_x0020_Link")).split(", ")[1] + '\">' + ($(this).attr("ows_Edit_x0020_Link")).split(", ")[1] + '</a></td>';
tdHtml = tdHtml + "<td style=\"width:83px;\">" + $(this).attr("ows_Priority") + "</td>";
tdHtml = tdHtml + "<td style=\"width:63px;\">" + TopItem($(this).attr("ows_Top_x0020_Item_x003f_")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:300px;\">" + StringChk($(this).attr("ows_Purpose")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:125px;\">" + StringChk($(this).attr("ows_Item_x002d_Task_x0020_Order")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:40px;\">" + StringChk($(this).attr("ows_Mode")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:75px;\">" + StringChk($(this).attr("ows_Task_x0020_Type")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:150px;\">" + StringChk($(this).attr("ows_Work_x0020_Status")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_DueDate")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_DAK_x0020_Date")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:100px;\">" + StringChk($(this).attr("ows_DAK_x0020_No")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:300px;\">" + StringChk($(this).attr("ows_AssignedTo")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:125px;\">" + $(this).attr("ows_Money_x0020_Estimate") + "</td>";
tdHtml = tdHtml + "<td style=\"width:75px;\">" + StringChk($(this).attr("ows_ItemStatus")) + "</td>";
tdHtml = tdHtml + "<td style=\"width:100px;\">" + FormatDate($(this).attr("ows_Assign_x0020_Date")) + "</td>";
tdHtml = tdHtml + "</tr>";
$("#tasksUL").append(tdHtml);
});
}
});
Any help would be gratefully welcomed, and I thank you in advance.
任何帮助将不胜感激，我提前感谢你。
UPDATE: The latest incarnation of the code is below. I have added the "WHERE" clause as:
更新：代码的最新化身如下。我已将“WHERE”子句添加为：
"<Where><Eq><FieldRef Name=Assign_x0020_Date' /><Value Type='Date and Time'>IsNull</Value></Eq></Where>" +
But it still returns no results. I'm not sure at this point what I'm missing here.
但它仍然没有返回任何结果。我现在不确定我在这里缺少什么。
The query with the above addition looks like this:
具有上述添加的查询如下所示：
function loadPrioritizedList() {
$("#tasksUL").empty();
$().SPServices({
operation: "GetListItems",
webURL: myURL,
listName: targetListName,
CAMLViewFields: "<ViewFields><FieldRef Name='Priority_x0020_Number' /><FieldRef Name='Edit_x0020_Link' /><FieldRef Name='Priority' /><FieldRef Name='Top_x0020_Item_x003f_' /><FieldRef Name='Purpose' /><FieldRef Name='Item_x002d_Task_x0020_Order' /><FieldRef Name='Mode' /><FieldRef Name='Work_x0020_Status' /><FieldRef Name='DueDate' /><FieldRef Name='Task_x0020_Type' /><FieldRef Name='DAK_x0020_Date' /><FieldRef Name='DAK_x0020_No' /><FieldRef Name='AssignedTo' /><FieldRef Name='Money_x0020_Estimate' /><FieldRef Name='ItemStatus' /><FieldRef Name='Assign_x0020_Date' /></ViewFields>",
CAMLQuery: '<Query>' +
"<Where><Eq><FieldRef Name='Assign_x0020_Date' /><Value Type='Date and Time'>IsNull</Value></Eq></Where>" +
'<OrderBy>' +
'<FieldRef Name="Priority_x0020_Number" />' +
'</OrderBy>' +
'</Query>',
回答by AymKdn
Since I use my framework I don't really care about the CAML, but it should be something like :
由于我使用我的框架，我并不真正关心 CAML，但它应该是这样的：
<query>
<Query>
<Where>
<IsNull><FieldRef Name="Assign_x0020_Date"></FieldRef></IsNull>
</Where>
</Query>
</query>
BTW you may want to try my framework : http://aymkdn.github.io/SharepointPlus/The syntax will be (for the same thing) :
顺便说一句，您可能想尝试我的框架：http: //aymkdn.github.io/SharepointPlus/语法将是（对于同样的事情）：
function loadPrioritizedList() {
$("#tasksUL").empty();
$SP().list(targetListName).get({
fields:"Priority_x0020_Number,Edit_x0020_Link,Priority,Top_x0020_Item_x003f_,Purpose,Item_x002d_Task_x0020_Order,Mode,Work_x0020_Status,DueDate,Task_x0020_Type,DAK_x0020_Date,DAK_x0020_No,AssignedTo,Money_x0020_Estimate,ItemStatus,Assign_x0020_Date",
where:"Assign_x0020_Date = ''",
orderby:"Priority_x0020_Number DESC",
rowlimit:listrowlimit
}, function (xData) {
var tdHTML="";
for (var i=xData.length; i--;) {
tdHtml += "<tr class='sortable_row' id=" + xData[i].getAttribute("ID") + ">";
tdHtml += "<td style=\"width:60px;\">" + PriorityFormat(xData[i].getAttribute("Priority_x0020_Number")); + "</td>";
tdHtml += '<td style=\"width:49px;\"><a href=\"'+(xData[i].getAttribute("Edit_x0020_Link")).split(", ")[1] + '\">' + (xData[i].getAttribute("Edit_x0020_Link")).split(", ")[1] + '</a></td>';
tdHtml += "<td style=\"width:83px;\">" + xData[i].getAttribute("Priority") + "</td>";
tdHtml += "<td style=\"width:63px;\">" + TopItem(xData[i].getAttribute("Top_x0020_Item_x003f_")) + "</td>";
tdHtml += "<td style=\"width:300px;\">" + StringChk(xData[i].getAttribute("Purpose")) + "</td>";
tdHtml += "<td style=\"width:125px;\">" + StringChk(xData[i].getAttribute("Item_x002d_Task_x0020_Order")) + "</td>";
tdHtml += "<td style=\"width:40px;\">" + StringChk(xData[i].getAttribute("Mode")) + "</td>";
tdHtml += "<td style=\"width:75px;\">" + StringChk(xData[i].getAttribute("Task_x0020_Type")) + "</td>";
tdHtml += "<td style=\"width:150px;\">" + StringChk(xData[i].getAttribute("Work_x0020_Status")) + "</td>";
tdHtml += "<td style=\"width:100px;\">" + FormatDate(xData[i].getAttribute("DueDate")) + "</td>";
tdHtml += "<td style=\"width:100px;\">" + FormatDate(xData[i].getAttribute("DAK_x0020_Date")) + "</td>";
tdHtml += "<td style=\"width:100px;\">" + StringChk(xData[i].getAttribute("DAK_x0020_No")) + "</td>";
tdHtml += "<td style=\"width:300px;\">" + StringChk(xData[i].getAttribute("AssignedTo")) + "</td>";
tdHtml += "<td style=\"width:125px;\">" + xData[i].getAttribute("Money_x0020_Estimate") + "</td>";
tdHtml += "<td style=\"width:75px;\">" + StringChk(xData[i].getAttribute("ItemStatus")) + "</td>";
tdHtml += "<td style=\"width:100px;\">" + FormatDate(xData[i].getAttribute("Assign_x0020_Date")) + "</td>";
tdHtml += "</tr>";
}
$("#tasksUL").append(tdHtml);
}
});
回答by WCS
Ah, the answer was in the way the IsNull has to be used. I modified the code:
啊，答案是必须使用 IsNull 的方式。我修改了代码：
"<Where><Eq><FieldRef Name=Assign_x0020_Date' /><Value Type='Date and Time'>IsNull</Value></Eq></Where>" +
To:
到：
"<Where><IsNull><FieldRef Name=Assign_x0020_Date' /></IsNull></Where>" +
Thanks to this SO post: https://stackoverflow.com/a/1341301/2190871
感谢这篇 SO 帖子：https: //stackoverflow.com/a/1341301/2190871
