Explore our sites

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm new here and have some difficult to convert a complex Postgres query to Ruby. Here is the query:

SELECT  date_part('year', p.created_at) AS anio, 
date_part('month'::text, p.created_at) AS mes,
p.id_pais, pa.nombre AS nombre_pais, 
pr.id_region, re.nombre AS nombre_region,
co.id_provincia, pr.nombre AS nombre_provincia,
p.id_comuna, co.nombre AS nombre_comuna,
p.id_tipo_propiedad, p.id_modalidad,
count(*) AS total_propiedades,
sum((p.precio/mo.conversion_dolar)/p.dimension_propiedad) AS suma_precio_m2_dolar,
sum((p.precio/mo.conversion_dolar)/p.dimension_propiedad)/count(*) AS  promedio_precio_m2_dolar
FROM    propiedad AS p
INNER JOIN monedas AS mo ON (p.id_moneda = mo.id)
INNER JOIN comuna AS co ON (p.id_comuna = co.id)
INNER JOIN provincia AS pr ON (co.id_provincia = pr.id)
INNER JOIN region AS re ON (pr.id_region = re.id)
INNER JOIN pais AS pa ON (p.id_pais = pa.id)
WHERE   p.id_modalidad IS NOT NULL
AND p.created_at IS NOT NULL
AND p.precio > 1
AND p.dimension_propiedad > 1
GROUP BY date_part('year',p.created_at), date_part('month'::text, p.created_at), p.id_tipo_propiedad, 
    p.id_modalidad, p.id_pais, pr.id_region, co.id_provincia, p.id_comuna, pa.nombre, re.nombre, pr.nombre, co.nombre

If you can help me with this i'll appreciate it.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

I'd just leave something like that as plain SQL and hand it to select_rows:

connection.select_rows(%q{
  -- your big pile of SQL goes here
}).each do |row|
  # row will be an array of Strings in here
end

You'll have to unstringify things yourself but that shouldn't be terribly difficult. You could also use execute:

rows = connection.execute(%q{
  -- SQL goes here...
})
rows.each do |row|
  # row will be a hash
end

Your query is pulling a bunch of data out of the database that doesn't correspond to any particular model so there's not much point in trying to get ActiveRecord to produce that SQL. If the query was producing a model, then you'd use Model.find_by_sql (and still avoid trying to convince ActiveRecord to produce the SQL you're after).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.