SQL query to ruby on rails

Question

I am wandering if you can help me with this.
I have this query on SQL and I need to do it in my rails app.
These query's represent the same result and works good.

SELECT DISTINCT ON (i.id)   
i.id, i.den_cont, c.date_expired  
FROM Items i  
JOIN Calibrations c ON c.item_id = i.id  
ORDER BY i.id, date_expired desc

and

select      codigo, numero, den_cont, date_expired  
from        items  
join calibrations c  
on      items.id = c.item_id  
inner join (select item_id, MAX(date_expired) as FECHA  
from calibrations  
group by item_id) as i2  
on c.item_id = i2.item_id and c.date_expired = i2.FECHA

I have this, I need to change it to do the above..

@items = Item.joins(:calibrations).where('calibrations.date_expired <= ?', '2014/09/01')

Kamen · Accepted Answer · 2014-12-12 21:41:38Z

1

Does something like this help?

Items.join(:calibrations)
     .where('calibrations.date_expired <= ?', '2014-09-01')
     .order('items.id')
     .group('items.id', 'items.den_cont')
     .select('items.id', 'items.den_cont', 'MAX(calibrations.date_expired)')

I assume items.id is the primary key and then the second group argument is, just so you can select the den_cont column.

EDIT: This is also an option,

Items.join(:calibrations)
     .where('calibrations.date_expired <= ?', '2014-09-01')
     .order('items.id')
     .order('calibrations.date_expired DESC')
     .select('DISTINCT ON (items.id) items.id, items.den_cont, calibrations.date_expired AS colaboration_expired')

edited Dec 12, 2014 at 21:41

answered Dec 12, 2014 at 21:29

Kamen

6993 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ingzane Over a year ago

Tks T! but can you help me to showing this..because I have an error in the index method (undefined method each)... <% @items.each do |item| %> <td><%= item.codigo + '%03d' % item.numero.to_s %></td> <td><%= item.den_cont %></td> <td><%= item.marca %></td> <td><%= item.modelo %></td> <td><%= item.calibrations.pluck(:date_expired).max %></td>

Kamen Over a year ago

Sorry, but I can't help you based on the info you give me. Try to execute the code I suggest in rails console and see that the result is.

Collectives™ on Stack Overflow

SQL query to ruby on rails

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related