joins.rb

  def self.with_latest_document
    joins(" INNER JOIN
              contract_documents AS joined_contract_documents
              ON contracts.id = joined_contract_documents.contract_id
            INNER JOIN
              (
                SELECT max(created_at) AS created_at, contract_id, id, status
                FROM contract_documents
                WHERE status IN ('uploaded', 'approved')
                GROUP BY contract_id
              ) AS newest_contract_documents
              ON joined_contract_documents.contract_id = newest_contract_documents.contract_id
                AND joined_contract_documents.created_at = newest_contract_documents.created_at
          ")
    .select("contracts.*, joined_contract_documents.created_at AS document_created_at, joined_contract_documents.status as document_status")
  end

  def self.waiting_for_backoffice_approval
    r = with_latest_document
    r = r.open_status
    r = r.where("(contracts.status = 'LM') OR (contracts.status <> 'LM' AND joined_contract_documents.status = 'uploaded')")
  end

  def self.cancelled_and_waiting_for_backoffice_approval
    with_latest_document.where("contracts.status = 'ST' AND joined_contract_documents.status = 'uploaded'")
  end