Obtain Multidimensional Array (codeigniter) data?

Question

I had a case solved through the question of this link here in the O.R.:

Following the example of the above question, I have the following table tbl_devedor_contrato, with the following fields:

`id`
`plano`
`contrato`
`dt_contrato`
`dt_expiracao`
`crud`
`assessoria_id`
`empresa_id`
`importacao_id`

On the return of the object, I need to add an item to this object. This item will be called view, where to retrieve data from another table tbl_devedor_negociacao.

The output would be something like this:

`id = 1`
`plano = 10`
`contrato = 1`
`dt_contrato = 2017-09-08`
`dt_expiracao = 9999-09-08`
`crud = C`
`assessoria_id = 1`
`empresa_id = 1`
`importacao_id = 0`
`view = id = 1
       dt_negociacao = 1`
       atualizar = 1`
       id_finalizacao = 1`
       contrato_id = 1`
       crud = 1`

Turns out this item view, does not exist in the table tbl_devedor_contrato ?

So how can I create this item, since it does not exist in the table and assign values to it.

Below I have the relationship I need to do:

Model:

//Obter devedor por ID | Contrato
public function obter_por_id_contrato($id)
{
    $this->db->from('tbl_devedor_contrato');
    $this->db->where('devedor_id',$id);
    $this->db->select("tbl_devedor_contrato.*, IF(tbl_devedor_contrato.crud = 'C', 'R', 'C') as crud", FALSE);
    $query = $this->db->get();
    return $query->result_array();
}`

Answer 1

The work is a bit confusing at first, but, rewarding when you have an expected result, you need to use optimized mechanisms so that this assembly does not get low performance and does not access too much the database, with 3 sql and functions of PHP (array_filter), can have the expected result, I will propose a minimal example that can and should be reflected in your question upon the first reply:

Diagram of the database

these tables have 1-to-many relationships and need to be joined together to generate a json with all the information of a particular devedor ($devedor_id).

Classes

Debtor Contract

<?php

class Devedorcontrato_model extends CI_Model
{

    public function obter_contrato_devedor_id($id)
    {
        $this->db->from('tbl_devedor_contrato');
        $this->db->where('devedor_id',$id);
        $this->db->order_by('id');
        $query = $this->db->get();
        return $query->result_array();
    }

}

Devedor Negociações

<?php

class Devedornegociacao_model extends CI_Model
{

    public function obter_negociacao_devedor_id($id)
    {

        $this->db->from('tbl_devedor_negociacao');
        if (is_array($id))
        {
            $this->db->where_in('contrato_id',$id);
        }
        else
        {
            $this->db->where('contrato_id',$id);
        }
        $this->db->order_by('contrato_id');
        $query = $this->db->get();
        return $query->result_array();
    }

}

Debtor Installments

<?php

class Devedorparcelas_model extends CI_Model
{

    public function obter_parcelas_negociacao_id($id)
    {
        $this->db->from('tbl_devedor_parcela');
        if (is_array($id))
        {
            $this->db->where_in('negociacao_id',$id);
        }
        else 
        {
            $this->db->where('negociacao_id',$id);  
        }

        $this->db->order_by('negociacao_id');
        $query = $this->db->get();
        return $query->result_array();
    }

}

The class that will be responsible for managing all this information at once:

<?php

    class Devedordados_model extends CI_Model 
    {
        private $CI;

        public function __construct()
        {
            parent::__construct();
            $this->CI =& get_instance();
            $this->CI->load->model("devedor_model");
            $this->CI->load->model("devedoremail_model");
            $this->CI->load->model("devedorcontrato_model");
            $this->CI->load->model("devedornegociacao_model");
            $this->CI->load->model("devedorparcelas_model");
        }

        public function obter_todos_dados_por_id($id)
        {
            $result = array();

            //Contratos
            $result['contratos'] = $this->devedorcontrato_model
                                        ->obter_contrato_devedor_id($id);

            //Negociações
            $id_contratos = array_map(function($item)
            { return $item['id'];}, $result['contratos']);
            $negociacoes = $this->devedornegociacao_model
                                ->obter_negociacao_devedor_id($id_contratos);

            //Parcelas
            $id_parcelas = array_map(function($item)
            { return $item['id']; }, $negociacoes);
            $parcelas = $this->devedorparcelas_model
                             ->obter_parcelas_negociacao_id($id_parcelas);


            for($i = 0; $i < count($negociacoes); $i++)
            {
                $id = $negociacoes[$i]['id'];
                $negociacoes[$i]['parcelas'] = 
                    array_filter($parcelas, function($a) use ($id){
                        return $a['negociacao_id'] == $id;
                    });
            }
            for($i = 0; $i < count($result['contratos']); $i++)
            {
                $id = $result['contratos'][$i]['id'];
                $result['contratos'][$i]['negociacoes'] =
                    array_filter($negociacoes, function($a) use ($id){
                        return $a['contrato_id'] == $id;
                    });

            }           

            return $result;
        }

    }

in this summary method you notice that I have considered the 3 classes that are related as I have said and need to be united to the generation of the json, the logic and use of the array_filter to build relationships and finally call on controller:

public function de()
{
    $this->load->model('Devedordados_model');

    echo json_encode($this->Devedordados_model->obter_todos_dados_por_id(1),
                              JSON_PRETTY_PRINT);

}

• Models
• Auto-loading Models
• array_filter