ruby-on-rails ruby ruby-on-rails-4 ruby-on-rails-5

Rails 6 table association user table in association with items table

Hi I'm trying to create an app that has items users are selling. I have a table for Users selling the item and a table for items, but I'm a little confused about how I should setup the next table for the buyer. I have a separate table that is many to many between User and Item tracking user_id and item_id. Should I be creating a similar table tracking buyer_id and item_id? I want to be able to track what item has been bought from which user and vs versa. User and Buyers are from the same User table.

Thanks!

Edit:

class UsersController < ApplicationController
  def my_page
    @user = current_user
    @seller_items = current_user.seller_orders.map { |so| so.order_items.map { |oi| { item: oi.item } } }.flatten
    @seller_items.to_a
  end
end

Solution

A more complete answer, with less models, and named joins. You might want to "merge" the order and order_items tables, and remove the multiple if you're dealing with singular items for sale, e.g. cars, but for anything that is either bought in bulk or might be sold at the same time as something else you might want this layout:

generate your models:

rails g model User name:string
rails g model Item name:string
rails g model Order order_date:time status:string
rails g model OrderItem order:references item:references multiple:integer

modify create_order to add in the additional references:

  def change
    create_table :orders do |t|
      t.time :order_date, index: true, null: false
      t.string :status
      t.references :buyer, index: true, null: false, foreign_key: {to_table: :users}
      t.references :seller, index: true, null: false, foreign_key: {to_table: :users}

      t.timestamps
    end

migrate the models:

rake db:migrate
== 20201126090851 CreateUsers: migrating ======================================
-- create_table(:users)
   -> 0.0036s
== 20201126090851 CreateUsers: migrated (0.0039s) =============================

== 20201126090858 CreateItems: migrating ======================================
-- create_table(:items)
   -> 0.0030s
== 20201126090858 CreateItems: migrated (0.0032s) =============================

== 20201126091129 CreateOrders: migrating =====================================
-- create_table(:orders)
   -> 0.0077s
== 20201126091129 CreateOrders: migrated (0.0081s) ============================

== 20201126091209 CreateOrderItems: migrating =================================
-- create_table(:order_items)
   -> 0.0065s
== 20201126091209 CreateOrderItems: migrated (0.0067s) ========================

modify the models to add the joins:

app/models/user.rb
::::::::::::::
class User < ApplicationRecord
  has_many :buyer_orders, class_name: "Order", foreign_key: :buyer, inverse_of: :buyer
  has_many :seller_orders, class_name: "Order", foreign_key: :seller, inverse_of: :seller
end
::::::::::::::
app/models/item.rb
::::::::::::::
class Item < ApplicationRecord
  has_many :order_items, inverse_of: :item
end
::::::::::::::
app/models/order.rb
::::::::::::::
class Order < ApplicationRecord
  has_many :order_items, inverse_of: :order
  belongs_to :seller, class_name: "User", inverse_of: :seller_orders
  belongs_to :buyer, class_name: "User", inverse_of: :buyer_orders
end
::::::::::::::
app/models/order_item.rb
::::::::::::::
class OrderItem < ApplicationRecord
  belongs_to :order, inverse_of: :order_items
  belongs_to :item, inverse_of: :order_items
end

insert data:

User.create(name: "hello")
User.create(name: "again")
Item.create(name: "whatever")
Order.create(buyer: User.first, seller: User.last, order_date: Time.now())
OrderItem.create(item: Item.first, order: Order.first, multiple: 1)

test the output:

Check the status of the order:

2.7.0 :002 > Order.first
   (0.5ms)  SELECT sqlite_version(*)
  Order Load (0.2ms)  SELECT "orders".* FROM "orders" ORDER BY "orders"."id" ASC LIMIT ?  [["LIMIT", 1]]
 => #<Order id: 1, order_date: "2000-01-01 09:26:22", status: nil, buyer_id: 1, seller_id: 2, created_at: "2020-11-26 09:26:22", updated_at: "2020-11-26 09:26:22">

2.7.0 :003 > Order.first.seller
  Order Load (0.2ms)  SELECT "orders".* FROM "orders" ORDER BY "orders"."id" ASC LIMIT ?  [["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
 => #<User id: 2, name: "again", created_at: "2020-11-26 09:25:26", updated_at: "2020-11-26 09:25:26">

2.7.0 :004 > Order.first.buyer
  Order Load (0.2ms)  SELECT "orders".* FROM "orders" ORDER BY "orders"."id" ASC LIMIT ?  [["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
 => #<User id: 1, name: "hello", created_at: "2020-11-26 09:25:18", updated_at: "2020-11-26 09:25:18">

Check the "buyer orders" of the first user:

2.7.0 :013 > User.first.buyer_orders
  User Load (0.2ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Order Load (0.3ms)  SELECT "orders".* FROM "orders" WHERE "orders"."buyer_id" = ? LIMIT ?  [["buyer_id", 1], ["LIMIT", 11]]
 => #<ActiveRecord::Associations::CollectionProxy [#<Order id: 1, order_date: "2000-01-01 09:26:22", status: nil, buyer_id: 1, seller_id: 2, created_at: "2020-11-26 09:26:22", updated_at: "2020-11-26 09:26:22">]>

check the "seller orders" of the second user:

2.7.0 :014 > User.last.seller_orders
  User Load (0.2ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT ?  [["LIMIT", 1]]
  Order Load (0.3ms)  SELECT "orders".* FROM "orders" WHERE "orders"."seller_id" = ? LIMIT ?  [["seller_id", 2], ["LIMIT", 11]]
 => #<ActiveRecord::Associations::CollectionProxy [#<Order id: 1, order_date: "2000-01-01 09:26:22", status: nil, buyer_id: 1, seller_id: 2, created_at: "2020-11-26 09:26:22", updated_at: "2020-11-26 09:26:22">]>

for peace of minds sake, check that the first user doesn't have any seller orders:

2.7.0 :015 > User.first.seller_orders
  User Load (0.2ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Order Load (0.2ms)  SELECT "orders".* FROM "orders" WHERE "orders"."seller_id" = ? LIMIT ?  [["seller_id", 1], ["LIMIT", 11]]
 => #<ActiveRecord::Associations::CollectionProxy []>
2.7.0 :016 >

Controller

class UsersController < ApplicationController
  def my_page
    @user = current_user
    @seller_orders = current_user.seller_orders
    # remember that you might want to filter this in the future
    # from_date = params[:from_date].present? ? params[:from_date] : Time.at(0)
    # to_date = params[:to_date].present? ? params[:to_date] : Time.now()
    # @seller_orders = @seller_orders.where(order_date: from_date..to_date)
  end
end

view (I use haml)

%h1
  = @user.username
  Seller Orders

- @seller_orders.each do |so|
  %table.seller_order{id: "seller_order_#{so.id}"}
    %tr
      %th Order Date:
      %td= so.order_date
    %tr
      %th Buyer:
      %td= so.buyer.username
    %tr.spacer
      %td{colspan: 2} &nbsp;
    %tr
      %th Item
      %th Multiple
    - so.order_items.each do |oi|
      %tr
        %td= oi.item.name
        %td= oi.multiple

seller items

@seller_items = @seller_orders.map{|so| so.order_items.map{|oi| {multiple: oi.multiple, item: oi.item} }.flatten

or possibly (written from memory, not tested)

@seller_items = OrderItem.select("sum(order_items.multiple) as multiple, order_items.item_id as item_id").joins(:orders).joins(:buyer).where("users.id = ?", User.first.id).group("item_id")