recommendations for the proper use of querys

Question

recommendations for the proper use of querys

Navigation

#1 by (1 votes)

2

I'm using a query to search for records, I have two tables, one is "sale" and the other is "detallevet" (this refers to the details of the sale) by this query I proceed to do the search of sales in their corresponding model

  def self.busqueda_general(params)
  query = joins("left outer join detallevet on venta.Documento=detallevet.Docto and venta.RutaId=detallevet.RutaId")
                     .where("(venta.RutaId = :rutaId or :rutaId = '') AND (detallevet.Articulo = :articulo or :articulo = '')  AND (venta.CodCliente = :codcliente or :codcliente = '')",{rutaId: params[:search], articulo: params[:search3], codcliente: params[:search2]})
                     .distinct
  end

then I call this method in the controller in a method called search_sales

  def busqueda_ventas
   @detallevet = Detalleve.all
    @ventas = Vent.busqueda_general(params)

    respond_to do |format|
    format.js
    end
  end

sales are related to retail sales but not by a field sale_id that identifies them, but by means of two fields, one called document and another RutaId in both tables, both combinations must be equal to relate. in my view I am ordering all my sales with their details respectively, to do this in the controller method look for all the details in this way @detallevet = Detalleve.all and in the view I did a render as follows

<%= render '@detallevet.where(:Docto => vent.Documento, :RutaId => vent.RutaId) %>

as for the data shown:

 <td><%=vent.IVA%></td>
        <td><%[email protected](:Docto => vent.Documento, :RutaId => vent.RutaId ).sum(:DescMon)%></td>
        <td><%=vent.TOTAL%></td>
        <td><%[email protected](:Docto => vent.Documento, :RutaId => vent.RutaId).count%></td>

        <td><%[email protected](:Docto => vent.Documento, :RutaId => vent.RutaId).sum(:Pza)%></td>

but it seems to me that I am making use of bad practices placing that type of consultations in the view, and if so, how could I improve it?

UPDATED CODE:

  has_many :detallevet, -> (vent) { where(Docto: vent.Documento, RutaId: vent.RutaId) },
           class_name: 'Detalleve'

ruby-on-rails ruby

asked by LuisC 18.12.2016 в 22:06

source

1 answer

Send a grid entity by POST Convert xml to array or json in php

score 1 · Accepted Answer

I think the main problem with your implementation (apart from not following the MVC pattern as it corresponds to doing the query in the view), is that for each sale you are making 3 queries to the database, causing 3N + 1 queries , where N is the amount of sales to show (Eg: If your query @ventas = Vent.busqueda_general(params) is returning 20 records, to show all the information in the view you ended up making 61 queries to the database instead of just one).
I would take advantage of the join you are making in busqueda_general and I would add all those sum and count that you did in the query view (note the select ):

  def self.busqueda_general(params)
    select("venta.*, COUNT(detallevet.*) AS venta_count,
            SUM(detallevet.DescMon) AS sum_DescMon,
            SUM(detallevet.Pza) AS sum_Pza")
      .joins("LEFT OUTER JOIN detallevet ON venta.Documento=detallevet.Docto
              AND venta.RutaId=detallevet.RutaId")
      .where("(venta.RutaId = :rutaId OR :rutaId = '')
              AND (detallevet.Articulo = :articulo OR :articulo = '')
              AND (venta.CodCliente = :codcliente OR :codcliente = '')",
             rutaId: params[:search], articulo: params[:search3],
             codcliente: params[:search2])
      .group(:id)
      .distinct
  end

Then in the view I would call the summation and counter of each sale, based on the alias that I put with AS within select :

<td><%=vent.IVA%></td>
<td><%=vent.sum_DescMon)%></td>
<td><%=vent.TOTAL%></td>
<td><%=vent.venta_count%></td>
<td><%=vent.sum_Pza)%></td>

To access the details of a sale, simply put a has_many with conditions:

has_many :detalles, -> (vent) { where(Docto: vent.Documento, RutaId: vent.RutaId) },
         class_name: 'Detalleve'

then you call:

vent.detalles