ruby on rails - SQL nested query -

i have images table , locations table want retrieve list of images latest images each location within boundaries.

select * images      location_id in         (select id locations              latitude > 17.954 , latitude < 52.574              , longitude > -107.392 , longitude < -64.853) 

this nested query, achieve same join. works if want images each location, 1 image per location (the recent)

here main fields of these tables

  table "images"     integer  "id"     text     "image_name"     text     "caption"     integer  "location_id"     datetime "created_at"     datetime "updated_at"     integer  "view_count"    table "locations"     integer  "id"     text     "name"     float    "longitude"     float    "latitude"     datetime "created_at"     datetime "updated_at"     string   "city"     string   "address"     string   "province"     string   "country"     string   "post_code" 

any idea?

bonus points if there way using rails activerecord api

you need make use of aliases , aggregation in sub-query.

select * images img     location_id in        (select id locations            latitude > 17.954 , latitude < 52.574            , longitude > -107.392 , longitude < -64.853)     , created_at in        (select max(created_at) images img2               img2.location_id=img.location_id)