使用 ajax 的带有子行的闪亮数据表 [英] Shiny datatable with child rows using ajax
问题描述
我正在尝试使用数据表库来实现更多自定义.
I'm trying to use datatables library for shiny with more customization.
这是我正在尝试制作的示例.https://datatables.net/examples/api/row_details.html请注意，我在不同的 data.frame R 变量中有详细信息.像这样
Here is the example that I'm trying to make. https://datatables.net/examples/api/row_details.html Note that I have detail info in different data.frame R variables. like this
A= data.frame(Name = c("Airi Satou", "Angelica Ramos","Paul Byrd")
, Position = c("Accountant","Accountant", "CEO")
, Office = c("Tokyo", "Tokyo", "New York"))
A.detail= data.frame(Name = c("Airi Satou", "Angelica Ramos")
, Extension= c("5407c", "8422")
, salary = c(16000, 20000))
我不喜欢合并两个 data.frame 变量，如果可以不合并的话，因为计算时间.显然，有些行可能没有任何详细信息.
I don't like to merge two data.frame variables, if it is possible to do this without merge, because of the computation time. Obviously, some of rows may not have any details.
我可以选择数据表中的一行并将行信息作为输入绑定到 R(感谢 https://github.com/toXXIc/datatables.Selectable/)然后我可以从第二个 data.frame 变量中找到与 R 中所选行相关的详细信息.但我不知道如何将其发送回以显示在 html 上(在所选行下).我已经将第一个表绑定为 Shinyoutput，所以我不确定是否可以传递另一个数据来再次更改此输出.
I can select a row in data table and send the line information to R by binding this as input (thanks to https://github.com/toXXIc/datatables.Selectable/) Then I can find details relevant to selected line in R from the second data.frame variable. but I don't know how to send this back to show on the html (under the selected row). I already binded the first table as shinyoutput so I;m not sure if I can pass another data to change this output again.
也许我应该在单击详细信息按钮时使用 ajax 请求更多数据，但我不知道如何在闪亮的情况下进行 ajax 请求.
Maybe I should use ajax to request more data when detail button is clicked, but I don't know how to do ajax request in shiny.
推荐答案
在回答你的问题之前，我想指出当前版本的 Shiny (0.10.1) on CRAN 使用的是旧版本的 DataTables.js (1.0.9)，而您提到的示例使用 DataTables.js 1.10.DataTables 1.10 中有相当一部分 API 与 1.0.9 版本不兼容.
Before answering your questions, I would like to point out that the current version of Shiny (0.10.1) on CRAN uses an older version of DataTables.js (1.0.9), whereas the example you have mentioned uses DataTables.js 1.10. There is a considerable proportion of API in DataTables 1.10 that is incompatible with version 1.0.9.
您可以在 Github 上查看此拉取请求:https://github.com/rstudio/shiny/pull/558 ，提供 DataTables.js 1.10 支持.
You can check out this pull request on Github: https://github.com/rstudio/shiny/pull/558 , which provides DataTables.js 1.10 support.
首先，让我们离题一下，了解如何在 Shiny 中呈现数据表.
First, let's digress a little bit to understand how a data table is rendered in Shiny.
该示例使用 AJAX 请求从服务器 URL 中拉取"数据，然后将数据绑定到表模板.这就是所谓的服务器端数据呈现.
The example uses AJAX request to "pull" data from a server URL, and subsequently binds the data to the table template. This is what so-called server-side data rendering.
Shiny 还使用服务器端数据渲染.但是，您提供的示例与 Shiny 之间的主要区别在于，Shiny 中的数据传输对您来说是透明的.
Shiny also uses server-side data rendering. However, the major difference between the example you've provided and Shiny, is that the data transferring in Shiny is transparent to you.
从技术上讲，Shiny 通过调用 shiny:::registerDataObj() 为 AJAX 请求创建了一个 JSON API.您可以在此处找到构建自定义 AJAX 请求的示例:http://shiny.rstudio.com/gallery/selectize-rendering-methods.html .
Technically, under the hood, Shiny creates an JSON API for AJAX requests by calling shiny:::registerDataObj(). You can find an example of constructing your customized AJAX request here: http://shiny.rstudio.com/gallery/selectize-rendering-methods.html .
示例和 Shiny 之间的另一个区别(稍后将反映在 R 代码中)是它们如何将表内容编码为 JSON blob.该示例使用普通对象对每一行进行编码.例如，第一行编码为:
Another difference between the example and Shiny, which will later be reflected in the R code, is how they encodes the table content into a JSON blob. The example encodes each line using plain objects. For instance, the first row is encoded as:
<代码>{"name": "老虎尼克松","position": "系统架构师","salary": "$320,800","开始日期": "2011/04/25","office": "爱丁堡",分机":5421"},
而 Shiny 将 data.frame 的每一行编码为数组，例如，类似的东西
Whereas Shiny encodes each row of your data.frame as an array, e.g., something like,
<代码>[《老虎尼克松》、《系统架构师》、$320,800"、2011/04/25"、爱丁堡"、5421"]
原始 JSON 数据的差异会影响我们稍后实现format() 函数的方式.
The difference in the raw JSON data affects how we would implement the format() function later.
最后，该示例使用固定的 HTML Finally, the example uses a fixed HTML 注意:下面的 R 代码使用 Shiny 的开发分支，您可以在上面的拉取请求链接中找到它. 虽然我们无法决定将哪些列绑定到哪些数据，但我们可以通过在调用 Although we don't get to decide which columns to bind to what data, we can choose which columns to hide by specifying the 使用示例数据的 Shiny 应用示例如下: An example of a Shiny app using your example data is: 这里没有什么特别之处，只是我相应地更改了 There is nothing special here except that I've changed the 现在，如果您单击数据表的第一(空)列(因为我没有编写 CSS)，您应该能够看到扩展区域中显示的额外信息. Now that if you click on the first (empty) column (because I've written no CSS) of your data table, you should be able to see the extra information shown in the expanded area. 上述解决方案涉及向客户端发送所有信息，尽管在大多数用例中，用户可能不会费心查看隐藏信息.本质上，我们最终会向客户端发送大量冗余数据. Above solution involves sending all information to the client, although in most use cases the user may not bother to view the hidden information. Essentially we end up with sending a lot of redundant data to the client side. 更好的解决方案是在 Shiny 中实现一个 AJAX 请求处理程序，它只在需要时(即当用户请求时)返回信息. A better solution is to implement a AJAX request handler in Shiny, which only returns the information when needed (i.e. as the user requests). 要实现 AJAX 请求处理程序，可以使用 To implement a AJAX request handler, one can use 为了调用这个注册的请求处理程序，你需要先把这个 AJAX URL 发送到客户端. In order to call this registered request handler, you need to first send this AJAX URL to the client. 下面我提出了一个快速解决方案:基本上，您可以在网页上创建一个隐藏的 Below I hacked a quick solution: basically you create a hidden 之后，您可以发起任何普通的 AJAX 请求来获取您需要的数据. Afterward, you can initiate any normal AJAX requests to fetch the data you need. 这篇关于使用 ajax 的带有子行的闪亮数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ 模板来呈现数据表.您可能已经注意到，模板中只包含了可见的列(例如，分机号码列不在
模板中)；而 Shiny 为您创建模板，您无法决定如何执行数据绑定(例如
{ "data": "name" },).
<table> template to render the data table. You may have noticed that only visible columns are included in the template (for example, the Extension number column is not in the <table> template); whereas Shiny creates the template for you and you don't get to decide how your data binding (e.g. { "data": "name" },) is performed.DataTable() 时指定 columnDefs 选项来选择要隐藏哪些列代码>功能.您可以通过将 https://datatables.net/reference/option/ 中定义的任何选项包装在 R 中的 list 中来传递它们.columnDefs options when you call the DataTable() function. You can pass whatever options that are defined in https://datatables.net/reference/option/ by wrapping them in a list in R.library(shiny)
format.func <- "
<script type='text/javascript'>
function format ( d ) {
return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
'<tr>'+
'<td>Full name:</td>'+
'<td>'+d[1]+'</td>'+
'</tr>'+
'<tr>'+
'<td>Extension number:</td>'+
'<td>'+d[4]+'</td>'+
'</tr>'+
'</table>';
}
</script>
"
shinyUI(
fluidPage(
h5("Data table"),
dataTableOutput("dt"),
tags$head(HTML(format.func))
)
)
format() 函数，因为如前所述，Shiny 将数据作为行数组而不是对象发送.format() function accordingly because, as mentioned before, Shiny sends data as row arrays instead of objects.library(shiny)
library(dplyr)
shinyServer(function(input, output, session) {
A <- data.frame(Name = c("Airi Satou", "Angelica Ramos","Paul Byrd"),
Position = c("Accountant","Accountant", "CEO"),
Office = c("Tokyo", "Tokyo", "New York"))
A.detail <- data.frame(Name = c("Airi Satou", "Angelica Ramos"),
Extension = c("5407c", "8422"),
Salary = c(16000, 20000))
# You don't necessarily need to use left_join. You can simply put every column,
# including the columns you would by default to hide, in a data.frame.
# Then later choose which to hide.
# Here an empty column is appended to the left to mimic the "click to expand"
# function you've seen in the example.
A.joined <- cbind("", left_join(A, A.detail, by="Name"))
columns.to.hide <- c("Extension", "Salary")
# Javascript uses 0-based index
columns.idx.hidden <- which(names(A.joined) %in% columns.to.hide) - 1
# Everytime a table is redrawn (can be triggered by sorting, searching and
# pagination), rebind the click event.
draw.callback <- "
function(settings) {
var api = this.api();
var callback = (function($api) {
return function() {
var tr = $(this).parent();
var row = $api.row(tr);
if (row.child.isShown()) {
row.child.hide();
tr.removeClass('shown');
}
else {
row.child(format(row.data())).show();
tr.addClass('shown');
}
}
})(api);
$(this).on('click', 'td.details-control', callback);
}"
# wrap all options you would like to specify in options=list(),
# which will be converted into corresponding JSON object.
output$dt <- renderDataTable(A.joined,
options=list(
searching=F,
columnDefs=list(
list(targets=0,
title="", class="details-control"),
list(targets=columns.idx.hidden,
visible=F)
),
drawCallback=I(draw.callback)
))
})
session$registerDataObj.此函数在唯一 URL 处注册请求处理程序，并返回此 URL.session$registerDataObj. This function registers a request handler at a unique URL, and returns this URL. 元素，您可以在该元素上绑定一个 change 事件侦听器.Shiny 服务器通过函数调用 session$sendInputMessage 向客户端发送消息来更新此 元素的值.收到消息后，它会更改 元素的值，从而触发事件侦听器.然后我们就可以正确设置AJAX请求URL了<input> element on the webpage on which you can bind a change event listener. The Shiny server updates this <input> element's value by sending a message to the client via the function call session$sendInputMessage. Once the message is received, it changes the value of the <input> element, triggering the event listener. We can then set up the AJAX request URL properlylibrary(shiny)
format.func <- "
<script type='text/javascript'>
var _ajax_url = null;
function format ( d ) {
// `d` is the original data object for the row
return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
'<tr>'+
'<td>Full name:</td>'+
'<td>'+d.Name+'</td>'+
'</tr>'+
'<tr>'+
'<td>Extension number:</td>'+
'<td>'+d.Extension+'</td>'+
'</tr>'+
'</table>';
}
$(document).ready(function() {
$('#ajax_req_url').on('change', function() { _ajax_url = $(this).val()});
})
</script>
"
shinyUI(
fluidPage(
# create a hidden input element to receive AJAX request URL
tags$input(id="ajax_req_url", type="text", value="", class="shiny-bound-input", style="display:none;"),
h5("Data table"),
dataTableOutput("dt"),
tags$head(HTML(format.func))
)
)
server.R
library(shiny)
library(dplyr)
shinyServer(function(input, output, session) {
# extra more.details dummy column
A <- data.frame(more.details="", Name = c("Airi Satou", "Angelica Ramos","Paul Byrd"),
Position = c("Accountant","Accountant", "CEO"),
Office = c("Tokyo", "Tokyo", "New York"))
A.detail <- data.frame(Name = c("Airi Satou", "Angelica Ramos"),
Extension = c("5407c", "8422"),
Salary = c(16000, 20000))
draw.callback <- "
function(settings) {
var api = this.api();
var callback = (function($api) {
return function() {
var tr = $(this).parent();
var row = $api.row(tr);
if (row.child.isShown()) {
row.child.hide();
tr.removeClass('shown');
}
else {
// we can use the unique ajax request URL to get the extra information.
$.ajax(_ajax_url, {
data: {name: row.data()[1]},
success: function(res) {
row.child(format(res)).show();
tr.addClass('shown');
}
});
}
}
})(api);
$(this).on('click', 'td.details-control', callback);
}"
ajax_url <- session$registerDataObj(
name = "detail_ajax_handler", # an arbitrary name for the AJAX request handler
data = A.detail, # binds your data
filter = function(data, req) {
query <- parseQueryString(req$QUERY_STRING)
name <- query$name
# pack data into JSON and send.
shiny:::httpResponse(
200, "application/json",
# use as.list to convert a single row into a JSON Plain Object, easier to parse at client side
RJSONIO:::toJSON(as.list(data[data$Name == name, ]))
)
}
)
# send this UNIQUE ajax request URL to client
session$sendInputMessage("ajax_req_url", list(value=ajax_url))
output$dt <- renderDataTable(A,
options=list(
searching=F,
columnDefs=list(
list(targets=0,
title="", class="details-control")
),
drawCallback=I(draw.callback)
))
})
登录
关闭
