Can anyone tell me how to convert this query to PostgreSQL
routes_controller.rb
@routes = Route.joins([:departure_location, :destination_location]).where("mdm_locations.name like ? or destination_locations_mdm_routes.name like ?" , "%#{k}%", "%#{k}%")
routes.rb (model)
module Mdm
class Route < ActiveRecord::Base
belongs_to :uom
belongs_to :distance_uom, :class_name => "Uom", :foreign_key => "distance_uom_id"
belongs_to :location
belongs_to :departure_location, :class_name => "Location", :foreign_key => "departure"
belongs_to :destination_location, :class_name => "Location", :foreign_key => "destination"
has_many :voyages, :dependent => :restrict
attr_accessible :description, :distance, :distance_uom_id, :departure, :std_consm, :destination, :uom_id
validates_presence_of :departure, :destination
end
end
Error :
PG::Error: ERROR: operator does not exist: integer = character varying
LINE 1: ...NNER JOIN "mdm_locations" ON "mdm_locations"."id" = "mdm_rou...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(*) FROM "mdm_routes" INNER JOIN "mdm_locations" ON "mdm_locations"."id" = "mdm_routes"."departure" INNER JOIN "mdm_locations" "destination_locations_mdm_routes" ON "destination_locations_mdm_routes"."id" = "mdm_routes"."destination" WHERE (LOWER(mdm_locations.name) like '%futong%' or LOWER(destination_locations_mdm_routes.name) like '%futong%')
