CODEWITHSUNDEEP
ruby-on-rails
johan
johan

Reputation: 721

chain and order from different models

I have 3 models

I want sort them in a table in my Products index by Category title, then by product title, then by size

Caegories | Product | Size |
----------------------------
   A      |   A     | S    |
   A      |   A     | M    |
   A      |   B     | S    |
   A      |   C     | S    |
   B      |   A     | S    |
   B      |   A     | M    |
   B      |   B     | S    |
   B      |   C     | S    |

How I am supposed to chain and order all this? is it possible?

update

create_table "categories", force: :cascade do |t|
    t.string "title", limit: 100, null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end


create_table "products", force: :cascade do |t|
    t.string "title", limit: 150, null: false
    t.decimal "price", precision: 15, scale: 2, default: "0.0", null: false
    t.text "description"
    t.bigint "category_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "color"
    t.integer "user_id"
    t.json "attachments"
    t.index ["category_id"], name: "index_products_on_category_id"
  end

  create_table "sizes", force: :cascade do |t|
    t.string "size_name"
    t.integer "quantity"
    t.bigint "product_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["product_id"], name: "index_sizes_on_product_id"
  end

I think I'd rather start ordering from Size in order to display each size... But I dont know how...

index.html.erb

<% "my query???".each do |size| %>
    <td><%= size.product.category.title %></td>
    <td><%= size.product.title %></td>
    <td><%= size.product.price %></td>
    <td><%= size.size_name %></td>
    <td><%= size.quantity %></td>
<% end %>

Upvotes: 1

Views: 38

Answers (2)

SRack
SRack

Reputation: 12203

While @ray's answer is solid for the most part, I think you'll need to plug in something extra to get the sizes ordered as expected, i.e. 'small, medium, large'.

In ray's answer, this would be alphabetical, while I imagine you'd want this to be from small to large or vice versa.

This would make your query look something like the following:

Product.joins(:category, :sizes).order("categories.title, title").order("CASE sizes.size_name WHEN 'SMALL' THEN 'a' WHEN 'MEDIUM' THEN 'b' WHEN 'LARGE' THEN 'c' ELSE 'z' END ASC")

I've chained order separately for the CASE statement for readability, though you could also do something like:

Product.joins(:category, :sizes).order("categories.title, title, CASE sizes.size_name WHEN 'SMALL' THEN 'a' WHEN 'MEDIUM' THEN 'b' WHEN 'LARGE' THEN 'c' ELSE 'z' END ASC")

Personally speaking, I'd go a step further with this and store the sizes as an enum column in the db if at all possible: 

enum size_name: [:small, :medium, :large] 
# Or enum size_name: { small: 0, medium: 1, large: 2 }

This makes the code very simple as the enum column stores an integer in order of the above:

Product.joins(:category, :sizes).order("categories.title, title, sizes.size_name")

Finally, this has bonus points: you'll have access to methods such as Size.medium, size.medium?, size.large! and so on :)

Hope this helps - let me know if you've any questions or comments!

Edit based on update to question

To start with the Size, you would tweak to one of the following:

Size.joins(product: :category).order("CASE sizes.size_name WHEN 'SMALL' THEN 'a' WHEN 'MEDIUM' THEN 'b' WHEN 'LARGE' THEN 'c' ELSE 'z' END ASC").order("categories.title, products.title")

Or with enum: 

Size.joins(product: :category).order("sizes.size_name, categories.title, products.title")

Upvotes: 2

ray
ray

Reputation: 5552

I did not test following but must work,

I did not get your sizes table schema properly. Assuming Size model have column type to store values like L(large), S(small) & M(medium). 

Product.joins(:category, :sizes).order('categories.title, title, sizes.type')

Make sure your associations and table schema are proper

Note: AS per your updated schema,

Product.joins(:category, :sizes).order('categories.title, title, sizes.size_name')

Upvotes: 2

Related Questions