Search through a query

2

I am doing a search engine for my application, it is a search form that handles two parameters: by Route and by Product, but the user can place a parameter or can place both in the search, my problem is that if the user selects one parameter, the other parameter is null and in the query it will find me where said field is null. I can not get the records unless I send both parameters, how could I solve this? here I leave my query:

   @ventas = Vent.joins('left outer join detallevet on venta.Documento=detallevet.Docto')
                    .where('venta.IdEmpresa = ? AND venta.RutaId = ? AND detallevet.Articulo = ?',current_usuario.empresa_id, params[:search], params[:search3])
                    .distinct
    
asked by LuisC 13.12.2016 в 02:52
source

2 answers

2

First thing, in general it is bad practice to put the query directly in the controller, that is logical that corresponds to the layer of the model, so the first change I would make would be this:

  @ventas = Vent.search(params)

then define a method in your model where there is a main query in which the common elements of the query are defined. Then you add conditional queries to it with if as you require it:

  def self.search(params)
    query = joins('left outer join detallevet on venta.Documento=detallevet.Docto')
            .where('venta.IdEmpresa = ?', current_usuario.empresa_id).distinct
    query = query.where('venta.RutaId = ?', params[:search]) if params[:search].present?
    query = query.where('detallevet.Articulo = ?', params[:search3]) if params[:search3].present?
    query
  end

By default in activerecord the calls are of type lazy , so until you begin to iterate or review the results of a query in general, you can continue chaining more queries.
Now, if you want something else, you could use searchlight which allows you to build searches based on the search parameters you receive from the form. Practically the same as I explained in the example, but much more orderly.

    
answered by 13.12.2016 / 15:14
source
1

I hope that the following gives you a clear idea of how you could revolve that problem.

What I would recommend doing in that situation would be to create a series of conditions in which you identify if the RUTA parameter is basic but the PRODUCT parameter if it has any value the query will be the following:

@ventas = Vent.joins('left outer join detallevet on venta.Documento=detallevet.Docto')
                    .where('venta.IdEmpresa = ? AND detallevet.Articulo = ?',current_usuario.empresa_id, params[:search3])
                    .distinct

In case the PRODUCT parameter arrives empty but the RUTA parameter is with a value, the query will be as follows:

@ventas = Vent.joins('left outer join detallevet on venta.Documento=detallevet.Docto')
                    .where('venta.IdEmpresa = ? AND venta.RutaId = ?',current_usuario.empresa_id, params[:search)
                    .distinct

In case the user puts the two parameters filled with some value, the query will be as follows:

@ventas = Vent.joins('left outer join detallevet on venta.Documento=detallevet.Docto')
                    .where('venta.IdEmpresa = ? AND venta.RutaId = ? AND detallevet.Articulo = ?',current_usuario.empresa_id, params[:search], params[:search3])
                    .distinct

That way you will get the results as you wish. I do not see the need to put all the code with the conditions since what you really need is an idea of how to solve that problem, the implementation I do not think is a bigger problem for you.

    
answered by 13.12.2016 в 03:21