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,