Shipment has_many :movements Movement belongs_to :shipment @shipments = Shipment.latest_movement scope :latest_movement, -> do joins( <<-'QUERY'.strip_sql LEFT OUTER JOIN movements ON shipments.id = movements.shipment_id AND movements.moved_at = ( SELECT MAX(movements.moved_at) FROM movements WHERE shipments.id = movements.shipment_id ) QUERY ).includes(:movements, :origin, :destination, :location) end

This code on the shipments page displays a list with data on shipment and its movement with the most recent movement.moved_at .

The problem is that if the shipment has two movements with the same movement.moved_at then a duplicate appears on the shipments list page and the same shipment displayed twice.

As in the sql query, add another condition to MAX(movements.moved_at) , so that if the maximum date of movements.moved_at found in two or more movements , one of them would have one with which movements.created_at more

UPDATE: Complete Rails Request

 @shipments = Shipment.visible_to(current_user).latest_movement.filter(filter_params) .reorder(sort).page(params[:page]) Started GET "/shipments?mawb=262-12345678&origin=&destination=&moved_at=&location=&status_code=&flight_data=&search="

in SQL, the query looks like this

 SELECT COUNT(*) FROM "shipments" LEFT OUTER JOIN movements ON shipments.id = movements.shipment_id AND movements.moved_at = ( SELECT MAX(movements.moved_at) FROM movements WHERE shipments.id = movements.shipment_id ) WHERE shipments.mawb = '262-12345678'

PostgreSQL database

    2 answers 2

    Well, the postgress supports window functions, so there will be something like

     AND (movements.moved_at, movements.created_at) = ( SELECT x.moved_at, x.created_at FROM ( SELECT movements.shipment_id , movements.moved_at , movements.created_at , ROW_NUMBER() OVER (PARTITION BY movements.shipment_id ORDER BY movements.moved_at DESC , movements.created_at DESC ) rn FROM movements ) x WHERE x.rn = 1 AND shipments.id = x.shipment_id )

    although it is wiser to do all this with a subquery in the FROM section, and not correlated in the WHERE section.

      It is possible to replace the MAX(moved_at) selection with a selection of a unique field (I selected the primary key) of one record ( LIMIT 1 ) from the set, sorted by descending moved_at , and then created_at .

       scope :latest_movement, -> do joins( <<-'QUERY'.strip_sql LEFT OUTER JOIN movements ON shipments.id = movements.shipment_id AND movements.id = ( SELECT movements.id FROM movements WHERE shipments.id = movements.shipment_id ORDER BY movements.moved_at DESC, movements.created_at DESC LIMIT 1 ) QUERY ).includes(:movements, :origin, :destination, :location) end

      ... although I do not really like this decision. But it is, intuitively, not slower than what you have.