render related data

Question

render related data

Navigation

#1 by (2 votes)

3

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 the sales in its corresponding model:

  def self.busqueda_general(params)
  query = select('[venta].Id,[venta].RutaId,[venta].VendedorId,[venta].CodCliente,[venta].Documento,[venta].Fecha,[venta].TipoVta,[venta].DiasCred,[venta].CreditoDispo,[venta].Saldo,[venta].Fvence,[venta].SubTotal,[venta].IVA,[venta].IEPS,[venta].TOTAL,
            [venta].Cancelada, COUNT([detallevet].DescMon) AS venta_count,
              SUM(detallevet.Precio) AS sum_Precio,
              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')
       .group(:Id,:RutaId,:VendedorId,:CodCliente,:Documento,:Fecha,:TipoVta,:DiasCred,:CreditoDispo,:Saldo,:Fvence,:SubTotal,:IVA,:IEPS,:TOTAL,:Cancelada)
       .where("(venta.RutaId = :rutaId or :rutaId = '') AND (detallevet.Articulo = :articulo or :articulo = '')  AND (venta.CodCliente = :codcliente or :codcliente = '') AND (venta.IdEmpresa = :idempresa)",{rutaId: params[:search], articulo: params[:search3], codcliente: params[:search2], idempresa: params[:search6]})
       .distinct
  query = query.where('venta.Fecha >= ? AND venta.Fecha <= ?', (params[:search4].to_date).strftime('%Y-%m-%d'), (params[:search5].to_date).strftime('%Y-%m-%d')) if params[:search4].present? and params[:search5].present?
  query
   end

With this I am looking for all those sales related to your details. What I want to do now is show the details of each sale through a render. I made the relationship in the sales model:

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

the details are related to the sales only through the combination of two fields, which is the "RutaId" and the "Document", I am trying to render this way:

'<% vent.detallevet.each do |d| %>
 <%= d.Articulo%>
 <% end %>'

but it shows me this error:

 ActionView::Template::Error (TinyTds::Error: Invalid column name 'vent_id'.: EXEC sp_executesql N'SELECT [detallevet].* FROM [detallevet] WHERE [detallevet].[vent_id] = @0 AND [detallevet].[Docto] = @1 AND [detallevet].[RutaId] = @2', N'@0 int, @1 int, @2 int', @0 = 710988, @1 = 16138782, @2 = 161):

I think you are looking for a vent_id (vent is the singular of the table sale) in detallevet (my detail table, the singular is detalleve), this field vent_id does not exist because both tables are only related by RutaId and Document. How could I solve it?

query ruby-on-rails ruby

asked by LuisC 28.12.2016 в 16:25

source

1 answer

Register ADO and VB 6.0 records Select in java MySQL

score 2 · Answer 1

By default when you put a relationship between two tables, rails always looks for an id that associates the tables and assumes that the foreign key will have a name of% "#{nombre_de_tabla_en_minuscula}_id" and that the primary key will be id . Otherwise, you must specify both values. In your case, the relationship is made through two fields, you should do something like:

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

Take care that I got RutaId of where that you had inside the lambda and I defined it as primary / foreign key.