Limit the number of rows in the second table?

Asked

Viewed 176 times

0

Hello, good night!

I have two tables (Process and Movements). I need to generate a report with the Process data, along with the most recent progress. The query I wrote is returning me all the movements, repeating the process data. I’ve tried using $this->db->limit(1) but so, only returns me 1 single process of several that should appear.

Query in Process Model_class:

function get_processos_ativos() {        
        $this->db->join('andamento', 'fkcodprocesso=codprocesso', 'left'); 
        $this->db->select('*');
        $this->db->order_by("str_to_date(andamento.dtandamento, '%d-%m-%Y')", 'DESC', FALSE);
        return $this->db->get('processo')->result();
    }

View code:

<?php foreach ($processo as $proc) : ?>
    <div class="row"> 
        <div class="col-md-4" >                         
            <p><font  size="2" face="helvetica"><strong>Autos nº:</strong> <a href="<?= base_url('processo/processoclicked/' . $proc->codprocesso) ?>" target="_self"><font size="2" face="helvetica"><?php echo $proc->nprocesso; ?></font></a></strong></font></p>                         
        </div>  
        <div class="col-md-12">
            <p><font  size="2" face="helvetica"><strong>Comarca:</strong> <?php echo $proc->comarca; ?> - <?php echo $proc->numerovara; ?> <?php echo $proc->vara; ?></font></p> 
        </div>      
    </div>   
    <!-- ...vários outros dados da tabela processo. -->


    Aqui é o dado onde eu quero que só apresente o último registro da tabela Andamentos:    

    <div class="row">
        <div class="col-md-12"    >                        
            <p align="justify"><font  size="2" face="helvetica"><strong>Último Andamento:</strong> <?php echo $proc->dtandamento; ?> - <?php echo $proc->descricao; ?></font></p>
        </div>  
    </div>                                             
    <hr>
<?php endforeach; ?>
php sql codeigniter

  • What query are you using to retrieve data from the database? I think that the solution will pass more easily by query than by PHP code itself.

  • @Joãomartins at the moment this one from the top, which brings me all the movements of each process, it occurs that I only need the last record of the table movements: Function get_processos_actives() ' $this->db->Join('progress', 'fkcodprocess=codprocess', 'left'); $this->db->select('*'); $this->db->order_by("str_to_date(progress.dtprogress, '%d-%m-%Y')", 'DESC', FALSE); Return $this->db->get('process')->result(); }

1 answer

1

If your goal is just to limit the results with no further conditions you were on the right track and you should use

$this->db->limit(N) being N the number of lines you want to display

  • then @Nunomaximiano lets me explain better, 1 Process has several movements, it occurs that I need to display several processes and the last progress of each one, when I use the $this->db->limit(1); it only returns me a single record, in case a single process, where q needs to display multiple processes. Gave the impression?

  • I think I’ve figured out the problem in order to solve this problem you’ll need to use sub-queries instead of Join $this->db->select('p.*, (select a.campo_a_apresentar from andamento a where a.idProcesso = p.id limit 1) AS campo_a_Apresentar ');&#xA;$this->db->order_by("str_to_date(andamento.dtandamento, '%d-%m-%Y')", 'DESC', FALSE);&#xA;$this->db->from('processo p');&#xA;return $this->db->get()->result();

  • thanks for the return, but it seems that Codeigniter 3.x no longer accepts Subqueries??

Browser other questions tagged php sql codeigniter

You are not signed in. Login or sign up in order to post.