如何显示与父记录相关的子记录? [英] How to display the child record related to the parent record?

问题描述

我正在使用CodeIgniter和带有子行的数据表.我有一张桌子，记录是:-

I am using CodeIgniter and data table with child row. I have a table and records are: -

id| Name    |  role  | leaders
1 | A       |  1     | 1
2 | B       |  2     | 1
3 | C       |  2     | 1
4 | D       |  3     | 2
5 | E       |  3     | 2
6 | F       |  4     | 3
7 | G       |  4     | 3

注意:领导者值是id值.

Note: leaders value are the id value.

现在，我必须显示角色为1,2,3的父记录. 就像我的Name A,B,C,D,E角色是1,2,2,3,3

Now I have to display the parent records which the role is 1,2,3. Like I have Name A,B,C,D,E with roles are 1,2,2,3,3

A，B，C，D，E记录的显示类似于父记录，我必须显示与领导者ID相关的子记录.

A, B, C, D, E records are displaying like parent records and I have to display the child record related to the leader's id.

print_r($books_of_secondary)

例如:A是父记录，子记录是B, C.与B相同，父级和子级分别为D和E. C是父级，子级是F和G所以我的输出如下所示

For example: A is the parent and child records are B, C. Same as on B is the parent and child are D and E. C is the parent and child are F and G So my output is like below

id| Name       |  role  | leaders
1 | A          |  1     | 1
    |
    |->B       |  2     | 1
    |->C       |  2     | 1

2 | B          |  2     | 1
    |
    |->D       |  3     | 2
    |->E       |  3     | 2

3 | c          |  2     | 1
    |
    |->F       |  4     | 3
    |->G       |  4     | 3

4 | D          |  3     | 2
    |
    |-> NO records

5 | E          |  3     | 2
    |
    |-> NO records

我尝试了一些代码，

控制器

public function team_members_get(){
    $draw = intval($this->input->get("draw"));
    $start = intval($this->input->get("start"));
    $length = intval($this->input->get("length"));
    $books = $this->Employee_model->getTotalList_of_TeamLeader();

    $data['draw'] = 1;
    $data['recordsTotal'] = count($books);
    $data['recordsFiltered'] = count($books);
    foreach ($books as $key => $row) 
    {
    $encryption_id=base64_encode($this->encryption->encrypt($row->id));
    $arr_result = array(
              // "Sr.No" => $n,
                    "id" => $encryption_id,
                    "TLname" => $row->firstname.' ' .$row->lastname,
                    "emp_role_name" => $row->emp_role_name,
                    "emp_teamLeader" => $row->team_leadername,

        );
      $array_secondary = array();
      $books_of_secondary = $this->Employee_model->getTotalList_of_TeamLeader_employee($row->team_leadername);
      foreach ($books_of_secondary as $key => $row) 
            {

                $arr_result2 = array(
                    "t_id" => base64_encode($this->encryption->encrypt($row->id)),
                    "t_name" => $row->firstname.' ' .$row->lastname,
                    "t_emp_role_name" => $row->emp_role_name,
                    "t_emp_teamLeader" => $row->team_leadername
                );
                $array_secondary[] = $arr_result2;
            }
         $arr_result['secondary'] =  $array_secondary;  
         $data['data'][] = $arr_result;
      }
    echo json_encode($data);
     exit;
     }

模型

public function getTotalList_of_TeamLeader(){
  $get_member ="access_role IN(1,2,3) AND is_archive=0";
    $this->db->select('*');
    $this->db->from('tbl_employee'); 
    $this->db->where($get_member);
        $query = $this->db->get();
        $res   = $query->result();       
       return $res;
  }
  public function getTotalList_of_TeamLeader_employee($team_leadername){
     $this->db->select('*');
    $this->db->from('tbl_employee');
    $this->db->where('team_leadername',$team_leadername);
        $query = $this->db->get();
        $res   = $query->result();
        return $res;
  }

Js

 function format(d) {
    // d is the original data object for the row var val;
     if(d.secondary.length == 0) {
        return "There are no Team members";
    }
    var display = '<table cellpadding="5" cellspacing="0" border="0"  width="100%">'; 

    for (val of d.secondary) {
     var s_action_set="<a href='' class='edit_color s_icon_set tooltip_'><img src='"+baseUrl+"/assets/images/icons/pending.png'><span class=tooltiptext_>Edit</span></a>";
        display += '<tr><td width="2%"></td>' + '<td>' + val.t_name + '</td>' + '<td>' + val.t_emp_role_name + '</td>'+ '</td>' + '<td>' + val.t_emp_teamLeader + '</td>'+ '</tr>';
    }
    display += '</table>';
    return display;
}

$(document).ready(function() {
    var oTable =$('#team_members_list').DataTable( {
        "responsive": true,
        "paging": true,
        "pageLength" : 10,
         "ajax": {
                    "url": baseUrl+ "/Employee_control/team_members_get",
                    "type": "POST"
                },
                "columns": [
                { "data": "TLname" },
                { "data": "emp_role_name" },
                { "data": "emp_teamLeader" },  
            ],
            });
    $('#team_members_list tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = oTable.row( tr );

        if ( row.child.isShown() ) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child( format(row.data()) ).show();
            tr.addClass('shown');

            //$('[data-toggle="tooltip"]', tr.next('tr')).tooltip();
        }
    } );
});

推荐答案

您必须编写自我联接查询.

you have to write self join query.

select te.name as leaderName,te1.name as childName from tbl_employee as te join tbl_employee as te1 on te.id = te1.leaders

此查询为您提供带子名的领导者姓名.

this query gives you leader name with child name.

这篇关于如何显示与父记录相关的子记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！