ActiveRecord query

Rails
1

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

2

On 11 November 2010 21:53, Ich [email protected] wrote:

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

3

I want all the “convenios” that don’t have been requested by a
particular user.

4

On Nov 12, 6:59am, Ich [email protected] wrote:

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)

5

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

6

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.