Rails how to get the top 4 values of each ids-CodePudding

I have a table which store each type of clothes we sale, a simplification could be :

TypeCloth (id, name_type, desc)

I have a second table storing all articles for each type of cloths

Cloth (id, name_cloth, desc, price, ... , type_cloth_id)

I want to do a query that let me show a quick view of the last 4 cloths of each type of cloths

I've done something like this :

@type_cloths = TypeCloth.all
@cloth = Cloth.where(type_cloth_id: @type_cloths.ids)

If I put a LIMIT 4 here I will just get 4 cloths. I would like to get 4 cloths of each types

I'm sure i'm missing something obvious here

CodePudding user response：

In PostgreSQL you can use window function

In your Cloth model you can define such scope

scope :first_each_type, ->(count) do
  ranked_clothes =
    select(
      "clothes.*",
      "dense_rank() OVER(PARTITION BY clothes.type_cloth_id ORDER BY clothes.id DESC) rank"
    )

  from("(#{ranked_clothes.to_sql}) AS clothes").where("rank <= ?", count)
end

And than call it as

Cloth.first_each_type(4)

If you need to return first N clothes by specified type, you can use something like this

scope :first_each_type, ->(count, type_cloth_ids = nil) do
  ranked_clothes =
    select(
      "clothes.*",
      "dense_rank() OVER(PARTITION BY clothes.type_cloth_id ORDER BY clothes.id DESC) rank"
    )

  ranked_clothes = ranked_clothes.where(type_cloth_id: type_cloth_ids) if type_cloth_ids

  from("(#{ranked_clothes.to_sql}) AS clothes").where("rank <= ?", count)
end

And than

Cloth.first_each_type(4)
Cloth.first_each_type(4, [1, 2])

If your collection is not so large, you can

Cloth.all.group_by(&:type_cloth_id).flat_map { _2.last(4) }

(SQLite has cluster index and you don't need to order records)

CodePudding user response：

Sqlite3 has support for window functions since version 3.25.0, so you could use ROW_NUMBER() for that;

Cloth
  .from(
    TypeCloth
      .joins(:cloths)
      .where(id: @type_cloths.ids)
      .select(
        'cloths.id',
        'ROW_NUMBER() OVER (PARTITION BY cloths.type_cloth_id) AS rn'
      ),
    :c
  )
  .joins('JOIN cloths on cloths.id = c.id')
  .where('rn <= 4');

You have to join cloths again with the subquery in from which is odd, but it's the Active Record way of using from. Or you could write it as a "raw" SQL query.