0

Okay, I have searched for an answer but I keep getting stuck on this problem. I am trying to get the max version for a group of items. This is what I have working, and it grabs all the correct items but does not grab the latest version of them.

    def index
       @project = Project.find(params[:project_id])

       @items = Item.group(:original_id).where(original_id: ItemOriginal.select("id").where(:project_id => @project.id))
    end

A project has many ItemOriginals and ItemOriginals have many Items.

I anything I try with max version or last created_at only grabs one record and not a record for each ItemOriginal

weredoomed
  • 1
  • 3

1 Answers1

0

I'm not positive, but I think you are asking, "Give me all this Project's ItemOriginals, and on each one include the version of its highest-versioned Item." In that case, this should work:

@items = @project.item_originals.select("item_originals.*, (SELECT MAX(version) FROM items i WHERE i.item_original_id = item_originals.id) AS max_version")

Then on each item you can get its max_version:

- @items.each do |item|
  = item.max_version

If you need the whole item and not just the version, you should switch to Postgres and use a LATERAL JOIN. :-)

Paul A Jungwirth
  • 23,504
  • 14
  • 74
  • 93
  • Sorry for the late reply, I will see if I can get this working. I had something working with arrays but once I try and use the search method for the ransack gem it all broke.. – weredoomed Feb 24 '16 at 22:41
  • Also the option of using Postgres is a no for my project unfortunately. – weredoomed Feb 24 '16 at 22:48
  • Sometimes it helps to work out the SQL before translating it into ActiveRecord. Getting the whole Item sounds a lot like this: http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/25534279 and also see http://illuminatedcomputing.com/posts/2015/02/postgres_lateral_join/ – Paul A Jungwirth Feb 25 '16 at 00:11
  • Thank you so much for the help! – weredoomed Feb 25 '16 at 16:37