I have this function in the Model, it is about the sales, but this makes reference to the table detalle_venta, which contains the products that were sold, then what I would like is to know how I can formulate the query so that I get the latest 5 best-selling products, thank you very much:
Query in the Model:
public function productosmasVendidos(){
$this->db->select("p.id, p.nombre, p.condicion, p.stock, p.precio,SUM(dv.cantidad) as totalVendidos");
$this->db->from("detalle_venta dv");
$this->db->join("productos p", "dv.producto_id = p.id");
$this->db->join("ventas v", "dv.venta_id = v.id");
$this->db->where("ACA NO SE QUE PONER");
$this->db->group_by("dv.producto_id"); //esto no se si esta bien
$resultados = $this->db->get();
return $resultados->result();
}
Ready here I leave the model code already solved:
public function getProductosmasVendidos(){
$this->db->select("p.id, p.codigo, p.nombre, p.condicion, p.stock, p.precio,SUM(dv.cantidad) as totalVendidos, c.nombre as categoria");
$this->db->from("detalle_venta dv");
$this->db->join("productos p", "dv.producto_id = p.id");
$this->db->join("ventas v", "dv.venta_id = v.id");
$this->db->join("categorias c","p.categoria_id = c.id");
$this->db->order_by("totalVendidos", "desc");
$this->db->limit(5);
$this->db->group_by("dv.producto_id");
$resultados = $this->db->get();
return $resultados->result();
}
This is the Controller:
public function index()
{
$data = array(
'productosmvendidos' => $this->Ventas_model->getProductosmasVendidos(),
);
$this->load->view("layouts/header");
$this->load->view("layouts/aside");
$this->load->view("admin/dashboard",$data);
$this->load->view("layouts/footer");
}
And here it would be in the view, I leave only the part of the div where the table will be:
<!--Productos mas vendidos-->
<div style="background-color: white;" class="row">
<div class="col-md-12">
<div align="center">
<div calss="col-md-12 content-header"> <strong>Top 10 Productos mas Vendidos</strong></div>
</div>
<div class="col-md-12">
<div class="table-responsive "></div>
<table class="table table-bordered table-hover table-striped">
<thead>
<tr>
<th>Codigo</th>
<th>Nombre</th>
<th>Categoria</th>
</tr>
</thead>
<tbody>
<?php if(!empty($productosmvendidos)):?>
<?php foreach($productosmvendidos as $pmv):?>
<tr>
<td><?php echo $pmv->codigo;?></td>
<td><?php echo $pmv->nombre;?></td>
<td><?php echo $pmv->categoria;?></td>
</tr>
<?php endforeach;?>
<?php endif;?>
</tbody>
</table>
</div>
</div>
</div>