Conditional issue using relationship

Question

I'm trying to search a column from another table using three tables in relationship.

In my view policy_vehicles I have a text_field_tag and want to search by "raz_soc".

My tables are:

Policy_vehicles
   |id|       |policy_id| 
    integer      integer
     100            1 
     200            2

Policies
   |id|    |client_id|
   integer  integer
    1        1
    2        2     

Clients
   |id|     |raz_soc|
    integer  varchar(255)
    1       MARATEX SAC
    2       ATT

This is my controller:

class PolicyManagement::PolicyController < ApplicationController

  def generate_print_per_vehicle
      params[:search_policy_id] =   Policy.find(:all,:joins => :client ,:conditions => ['raz_soc LIKE ?',"%#{params[:search_raz_soc]}%"  ])
      @policies= PolicyVehicle.find(:all,:joins => :policy, :conditions => ['policy_id = ?',params[:search_policy_id] ]) 
  end

end

This is my model:

class Policy < ActiveRecord::Base
  belongs_to :client
  has_many :policy_vehicles
end

class PolicyVehicle < ActiveRecord::Base
  belongs_to :policy
end

class Client < ActiveRecord::Base
  has_many :policies
end

This is my view where I'm trying to find by a column from another table:

<% form_tag :controller=>"policy_management/policy",:action=>"generate_print_per_vehicle" do %>
 Society:
   <%= text_field_tag "search_raz_soc",params[:search_raz_soc] %>
   <%= submit_tag "Buscar", :name => nil %>
<% end %>

My logs are:

  Mysql::Error: Operand should contain 1 column(s): SELECT `policy_vehicles`.* FROM `policy_vehicles`   INNER JOIN `policies` ON `policies`.id = `policy_vehicles`.policy_id  WHERE (policy_id = 166,1540,2822,4074)

It should be a search like this:

 Policy Load (3.1ms)   SELECT `policies`.* FROM `policies` INNER JOIN `clients` ON `clients`.id = `policies`.client_id WHERE (raz_soc = 'MARATEX SAC') 
 PolicyVehicle Load (0.3ms)   SELECT `policy_vehicles`.* FROM `policy_vehicles` INNER JOIN `policies` ON `policies`.id = `policy_vehicles`.policy_id WHERE (policy_id = 1) 

I tried this but is not working:

 #controller
 @policy =         Policy.find(:all,:joins => :client ,:conditions => ['raz_soc LIKE ?',params[:search_raz_soc]  ])
 @policies= PolicyVehicle.find(:all,:joins => :policy, :conditions => ['policy_id = ?',@policy    ])

#logs
 Policy Load (3.1ms)   SELECT `policies`.* FROM `policies` INNER JOIN `clients` ON `clients`.id = `policies`.client_id WHERE (raz_soc = 'MARATEX SAC') 
 PolicyVehicle Load (0.3ms)   SELECT `policy_vehicles`.* FROM `policy_vehicles` INNER JOIN `policies` ON `policies`.id = `policy_vehicles`.policy_id WHERE (policy_id = 70353610714140) 

I'm trying to do something like this

 select * from policy_vehicles where policy_id
    IN ( SELECT id FROM policies WHERE
    client_id IN (SELECT id FROM clients raz_soc = ?) )

Can someone can help me please?

Answer 1

Try this (I have explained what the queries return so make sure that this is what you want from them)

@policies =  Policy.find(:all,:joins => :client ,
             :conditions => ['raz_soc LIKE ?',"%#{params[:search_raz_soc]}%"] ) 
# returns an array of policy records based on the raz_soc condition on client 

@policy_vehicles = PolicyVehicle.find(:all,:joins => :policy,
                   :conditions => ['policy_id IN (?)',@policies] )
# returns an array of policy_vehicles that are associated with any record in the @policies array

Other option is to do it in a single query as:

@policy_vehicles = PolicyVehicle.joins(:policy => :client).
                   where('clients.raz_soc LIKE ?',"%#{params[:search_raz_soc]}%")