如何在不同表的数据表的单元格中呈现多行? [英] How to render multiple row in a cell in Datatables from a different table?

问题描述

我正在使用数据表创建表，但是在呈现数据时遇到了一些麻烦.我的表格结构是.

I am creating a table using datatables and I am having some trouble rendering data in it. My Table structures are.

TABLE_1
|------|------|-------|
|  ID  | NAME | PHONE |
|------|------|-------|
TABLE_2
|------|------------|----------|
|  ID  | TABLE_1_ID | CATEGORY |
|------|------------|----------|

这是我的PHP代码

$db  = new Database; // Database connection
$sql = "SELECT a.*, b.* FROM TABLE_1 a, TABLE_2 b WHERE a.ID = b.TABLE_1_ID";
$exe = $db->select($sql);
$result = array();
foreach ($exe as $rows) {
    $result[] = $rows;
}
echo json_encode($result);

这是我的JavaScript

This is my JavaScript

$('#example').DataTable({
    ajax: {
        url:"data.php",
        dataSrc:""
    },
    columns: [
        {data:"NAME"},
        {data:"CATEGORY"}
    ]
});

到目前为止，一切正常，数据已完美加载.但是问题是，假设我在TABLE_1中只有一行，而在TABLE_2中只有5行，其中我的数据表的TABLE_1.ID = TABLE_2.TABLE_1_ID和bcoz正在生成5行，但是我希望所有类别都在一个单元格中，并且我只想要一行而不是5.

Up to this point everything is working fine, the data is perfectly loaded. But the problem is, suppose I have only one row in TABLE_1 and 5 rows in TABLE_2 where TABLE_1.ID = TABLE_2.TABLE_1_ID and bcoz of this my datatable is generating 5 rows but I want all the categories in a single cell and I want only one row instead of 5.

我正在考虑在render函数中做一些事情，例如

I am thinking of doing some stuff inside the render function, like

$('#example').DataTable({
    ajax: {
        url:"data.php",
        dataSrc:""
    },
    columns: [
        {data:"NAME"},
        {
            data:"ID",
            render: function(data, type, row){
                // Some stuff to render 5 Category in a single cell
                // Using the ID from row.ID (maybe)
                // how to return 5 CATEGORY in this cell
            }
        }
    ]
});

但是我真的不知道这个过程，而google + stackoverflow + datatables论坛对我来说有点混乱bcoz我的Java语言不好. 你们可以帮我实现这一目标吗?为了在单个单元格中显示5个类别，我必须在呈现功能中编写哪种类型的代码或编写什么代码. 预先感谢.

But I really don't know the process and google + stackoverflow + datatables forum is little bit confusing for me bcoz I am not good in Javascript. Can you guys help me achieve this? What type of code or what code I have to write inside the render finction to display 5 CATEGORY in a single cell. Thanks in advance.

推荐答案

您可以在应用程序层中转换数据，以便在结果数组中仅包含表a的行以及相关的类别名称.

You can transform your data in your application layer so that in resultant array you will have only rows for table a along with related category names.

首先，您需要一个有序的结果集，例如

First you need an ordered result set like

select a.id,
  a.phone,
  a.name,
  b.category 
from table_1 a
join table_2 b 
  on a.id = b.table_1_id
order by a.id asc

然后遍历所有记录并烹饪数据集

Then loop through all records and cook your data set

$result = [];
$currentParent = false;
$data = null;
foreach ($rows as $row) {
    /* 
     * if id is changed then its a different record
     * prepare data for new row and create a comma separated string of category names
     */
    if ($currentParent != $row['id']) {
        if($data != null){ // for first and intermediate rows
            $result[]= $data;
        }
        $data = ['name'=> $row['name'], 'category'=> ''];
        $currentParent = $row['id'];
    }
    $data['category'] = empty($data['category']) ? $row['category']: $data['category'] .", ". $row['category'];
}
$result[]= $data; // add data for last row
echo json_encode($result);

结果数组看起来像

Array
(
    [0] => Array
        (
            [name] => item 1
            [category] => Cat 1, Cat 2, Cat3
        )

    [1] => Array
        (
            [name] => item 2
            [category] => Cat 1, Cat 2, Cat3
        )

    [2] => Array
        (
            [name] => item 3
            [category] => Cat 1, Cat 2, Cat3
        )

)

另一种速记方法(但不是首选方法)是在查询级别应用聚合方法，例如，如果您使用的是MySQL，则可以使用

Another shorthand way but not preferred is to apply aggregate methods on query level like if you are using MySQL you can use group_concat but it has a restriction of max character limit (which is adjustable).

这篇关于如何在不同表的数据表的单元格中呈现多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！