ActiveRecord query

I have these sql code in postgresql "SELECT * from convenios where id NOT IN (SELECT convenio_id from solicituds where usuario_id=?"

but don't find a way of used it in rails except find_by_sql.

There is a another way??

class Usuario < ActiveRecord::Base   belongs_to :persona   has_many :solicituds, :dependent => :destroy   has_many :convenios, :through => :solicituds   has_many :nivelesacceso   has_one :nomina end

class Convenio < ActiveRecord::Base   has_many :solicituds, :dependent => :destroy   has_many :personas, :through => :solicituds   validates_presence_of :nombre, :serie, :descripcion   validates_uniqueness_of :serie   validates_format_of :serie, :with => /cci|cue/   validates_length_of :serie, :is => 8 end

class Solicitud < ActiveRecord::Base   belongs_to :convenio   belongs_to :usuario end

I have these sql code in postgresql "SELECT * from convenios where id NOT IN (SELECT convenio_id from solicituds where usuario_id=?"

That does not look like valid sql to me. Can you explain in words what you are trying to achieve? I find this is often the best way of working out how to code something.

Colin

I want all the "convenios" that don't have been requested by a particular user.

This is not tested, but I think something like this should get you what you want. May need to tweak it a little bit.

Convenio.joins(:solicituds).where("solicituds.usuario_id <> ?", 1234)

Don't work for me. A last I use

   arreglo= Array.new     arreglo=solicituds.all(:select => "convenio_id").map(&:convenio_id)     arreglo<<-1     Convenio.where(["id NOT IN (?)",arreglo]) I'm not happy but works.

Please quote when replying.

Ich wrote in post #961576:

Don't work for me.

Are you using Rails 2 or 3? The syntax that you're responding to was for Rails 3 (or Rails 2 + Arel).

A last I use

   arreglo= Array.new     arreglo=solicituds.all(:select => "convenio_id").map(&:convenio_id)     arreglo<<-1     Convenio.where(["id NOT IN (?)",arreglo]) I'm not happy but works.

That's not even good SQL. I think you want something like this SQL:

SELECT * FROM convenios c LEFT JOIN solicituds s ON (s.convenio_id = c.id AND s.usuario_id = :user_id) WHERE s.id IS NULL

That gets everything in one query, and will be pretty easy to translate into AR find arguments, so you won't need find_by_sql here.

Best,