How to request query results in rails (request results "DISTINCT ON" with rails and postgres

Question

Short version: I would like to query the result of another query to select a more limited set of results. However, adding a where clause overwrites the first query, and does not work with the results, so I don't get the answers I need.

Details: I have two models, checks and ticks. Checks has_many ticks.

The first query uses DISTINCT ON and collects all checks and all associated ticks, but returns only the last tick. I have a job as a scope in a model.

In my controller

  def checklist
  #Filter the results by scope or return all checks with latest tick
  case params[:filter]
    when "duebylastresult"
      @checks = Check.mostrecenttickonly.duebylastresult
    when "duebydate"
      @checks = Check.mostrecenttickonly.duebydate
    else
      @checks = Check.mostrecenttickonly
    end
  end

In the model, the first area (working):

scope :mostrecenttickonly, -> {
includes(:ticks)
.order("checks.id, ticks.created_at DESC")
.select("DISTINCT ON (checks.id) *").references(:ticks)
}

Creates the following SQL:

  Parameters: {"filter"=>""}
  SQL (1.0ms)  SELECT DISTINCT ON (checks.id) *, 
"checks"."id" AS t0_r0, 
"checks"."area" AS t0_r1, "checks"."frequency" AS t0_r2, 
"checks"."showinadvance" AS t0_r3, "checks"."category" AS t0_r4, 
"checks"."title" AS t0_r5, "checks"."description" AS t0_r6, 
"checks"."created_at" AS t0_r7, "checks"."updated_at" AS t0_r8, 
"ticks"."id" AS t1_r0, "ticks"."result" AS t1_r1, 
"ticks"."comments" AS t1_r2, "ticks"."created_at" AS t1_r3, 
"ticks"."updated_at" AS t1_r4, "ticks"."check_id" AS t1_r5 
FROM "checks" LEFT OUTER JOIN "ticks" 
ON "ticks"."check_id" = "checks"."id"  
ORDER BY checks.id, ticks.created_at DESC

, , 3, :

   scope :duebylastresult, -> { where("ticks.result >= 3") }

SQL

  Parameters: {"filter"=>"duebylastresult"}
  SQL (1.0ms)  SELECT DISTINCT ON (checks.id) *, 
"checks"."id" AS t0_r0, 
"checks"."area" AS t0_r1, "checks"."frequency" AS t0_r2,
"checks"."showinadvance" AS t0_r3, "checks"."category" AS t0_r4, 
"checks"."title" AS t0_r5, "checks"."description" AS t0_r6, 
"checks"."created_at" AS t0_r7, "checks"."updated_at" AS t0_r8, 
"ticks"."id" AS t1_r0, "ticks"."result" AS t1_r1, 
"ticks"."comments" AS t1_r2, "ticks"."created_at" AS t1_r3, 
"ticks"."updated_at" AS t1_r4, "ticks"."check_id" AS t1_r5 
FROM "checks" LEFT OUTER JOIN "ticks" 
ON "ticks"."check_id" = "checks"."id" 
WHERE (ticks.result >= 3)  
ORDER BY checks.id, ticks.created_at DESC

, WHERE DISTINCT ON, " , = = 3", " " , >= 3 '.

, !

- , :

The Data:
Table Checks:
ID: 98 Title: Eire
ID: 99 Title: Land

Table Ticks:
ID: 1 CheckID: 98 Result:1 Date: Jan12
ID: 2 CheckID: 98 Result:5 Date: Feb12
ID: 3 CheckID: 98 Result:1 Date: Mar12
ID: 4 CheckID: 99 Result:4 Date: Apr12

First query returns the most recent result, like;
Check.ID: 98  Tick.ID: 3  Tick.Result: 1 Tick.Date: Mar12
Check.ID: 99  Tick.ID: 4  Tick.Result: 4 Tick.Date: Apr12

Second query currently returns the most recent result where the result is =>3, like;
Check.ID: 98  Tick.ID: 2  Tick.Result: 5 Tick.Date: Feb12
Check.ID: 99  Tick.ID: 4  Tick.Result: 5 Tick.Date: Apr12

When I really want:
Check.ID: 99  Tick.ID: 4  Tick.Result: 5 Tick.Date: Apr12

(ID 98 doesn't show as the last Tick.Result is 1).

Answer 1

, , :

    scope :just_a_test, -> {
    includes(:ticks)
    .order("checks.id")
    .where("ticks.created_at = (SELECT MAX(ticks.created_at) FROM ticks WHERE ticks.check_id = checks.id)")
    .where("ticks.result >= 3")
    .group("checks.id")
    }

Answer 2

, :mostrecenttickonly, .

, , , - :

check.rb

...
  scope :duebylastresult, -> {
    find_by_sql(
      'SELECT *
       FROM (SELECT checks.*,
                    ticks.id AS tick_ids,
                    ticks.date AS tick_date,
                    ticks.result AS tick_result,
                    dense_rank() OVER (
                      PARTITION BY checks.id
                      ORDER BY ticks.date DESC
                    ) AS tick_rank
             FROM checks
             LEFT OUTER JOIN ticks ON checks.id = ticks.check_id) AS ranked_ticks
       WHERE tick_rank = 1 AND tick_result >= 3;'
    )
  }
...

, , tick_rank, date checks.id .

SQL, ( WHERE) SELECT, , tick_rank = 1 .

, ( ranked_ticks), , . tick_rank 1, tick, >= 3.

---

edit: , , SQL, , , , ( , checks , , , ):

  scope :duebylastresult, -> {
    find_by_sql(
      'SELECT *
       FROM checks
       LEFT OUTER JOIN 
            (SELECT id AS tick_id,
                    check_id AS check_id,
                    date AS tick_date, 
                    result AS tick_result,
                    dense_rank() OVER (
                      PARTITION BY ticks.check_id
                      ORDER BY ticks.date DESC
                    ) AS tick_rank
             FROM ticks) AS ranked_ticks ON checks.id = ranked_ticks.check_id
       WHERE tick_rank = 1 AND tick_result >= 3;'
    )
  }