Class: AuctionFunCore::Relations::Auctions

Inherits:
Object
  • Object
show all
Defined in:
lib/auction_fun_core/relations/auctions.rb

Overview

SQL relation for auctions.

See Also:

Constant Summary collapse

KINDS = 
Types::Coercible::String.enum("standard", "penny", "closed")
STATUSES = 
Types::Coercible::String.enum("scheduled", "running", "paused", "canceled", "finished")

Instance Method Summary collapse

Instance Method Details

#all(page = 1, per_page = 10, options = {bidders_count: 3}) ⇒ Object 



43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 43

def all(page = 1, per_page = 10, options = {bidders_count: 3})
  raise "Invalid argument" unless page.is_a?(Integer) && per_page.is_a?(Integer)

  offset = ((page - 1) * per_page)

  read("
    SELECT a.id, a.title, a.description, a.kind, a.status, a.started_at, a.finished_at, a.stopwatch, a.initial_bid_cents,
    (SELECT COUNT(*) FROM (SELECT * FROM bids WHERE bids.auction_id = a.id) dt) AS total_bids,
    CASE
    WHEN a.kind = 'standard' THEN
      json_build_object(
        'current', a.minimal_bid_cents,
        'minimal', a.minimal_bid_cents,
        'bidders', COALESCE(
          json_agg(json_build_object('id', bi.id, 'user_id', users.id, 'name', users.name, 'value', bi.value_cents, 'date', bi.created_at) ORDER BY value_cents DESC)
          FILTER (where bi.id IS NOT NULL AND users.id IS NOT NULL), '[]'::json
        )
      )
    WHEN a.kind = 'penny' THEN
      json_build_object(
        'value', a.initial_bid_cents,
        'bidders', COALESCE(
          json_agg(json_build_object('id', bi.id, 'user_id', users.id, 'name', users.name, 'value', bi.value_cents, 'date', bi.created_at) ORDER BY value_cents DESC)
          FILTER (where bi.id IS NOT NULL AND users.id IS NOT NULL), '[]'::json
        )
      )
    WHEN a.kind = 'closed' THEN
      json_build_object('minimal', (a.initial_bid_cents + (a.initial_bid_cents * 0.10))::int)
    END as bids
  FROM auctions as a
  LEFT JOIN LATERAL (SELECT * FROM bids WHERE auction_id = a.id ORDER BY value_cents DESC LIMIT #{options[:bidders_count]}) as bi ON a.id = bi.auction_id
  LEFT JOIN users ON bi.user_id = users.id AND bi.auction_id = a.id
  GROUP BY a.id
  LIMIT #{per_page} OFFSET #{offset}")
end

#info(auction_id, options = {bidders_count: 3}) ⇒ Object 



79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 79

def info(auction_id, options = {bidders_count: 3})
  raise "Invalid argument" unless auction_id.is_a?(Integer)

  read("
    SELECT a.id, a.title, a.description, a.kind, a.status, a.started_at, a.finished_at, a.stopwatch, a.initial_bid_cents,
    (SELECT COUNT(*) FROM (SELECT * FROM bids WHERE bids.auction_id = #{auction_id}) dt) AS total_bids,
    CASE
    WHEN a.kind = 'standard' THEN
      json_build_object(
        'current', a.minimal_bid_cents,
        'minimal', a.minimal_bid_cents,
        'bidders', COALESCE(
          json_agg(json_build_object('id', bi.id, 'user_id', users.id, 'name', users.name, 'value', bi.value_cents, 'date', bi.created_at) ORDER BY value_cents DESC)
          FILTER (where bi.id IS NOT NULL AND users.id IS NOT NULL), '[]'::json
        )
      )
    WHEN a.kind = 'penny' THEN
      json_build_object(
        'value', a.initial_bid_cents,
        'bidders', COALESCE(
          json_agg(json_build_object('id', bi.id, 'user_id', users.id, 'name', users.name, 'value', bi.value_cents, 'date', bi.created_at) ORDER BY value_cents DESC)
          FILTER (where bi.id IS NOT NULL AND users.id IS NOT NULL), '[]'::json
        )
      )
    WHEN a.kind = 'closed' THEN
      json_build_object('minimal', (a.initial_bid_cents + (a.initial_bid_cents * 0.10))::int)
    END as bids
  FROM auctions as a
  LEFT JOIN LATERAL (SELECT * FROM bids WHERE auction_id = a.id ORDER BY value_cents DESC LIMIT #{options[:bidders_count]}) as bi ON a.id = bi.auction_id AND a.id = #{auction_id}
  LEFT JOIN users ON bi.user_id = users.id AND bi.auction_id = a.id
  WHERE a.id = #{auction_id}
  GROUP BY a.id")
end

#load_closed_auction_winners_and_participants(auction_id) ⇒ Hash

Retrieves the closed auction winner and other participating bidders for a specified auction.

This method queries the database to fetch the winner based on the highest bid and collects an array of other participants who placed bids in the auction, all except for the winner. The method returns structured data that includes the auction ID, winner’s ID, total number of bids, and a list of participant IDs.

Returns:

  • (Hash) —

    a hash containing details about the auction, including winner and participants:

    • :id [Integer] the ID of the auction

    • :winner_id [Integer] the ID of the winning bidder

    • :total_bids [Integer] the total number of bids placed in the auction

    • :participants [Array<Integer>] an array of user IDs of the other participants, excluding the winner



190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 190

def load_closed_auction_winners_and_participants(auction_id)
  raise "Invalid argument" unless auction_id.is_a?(Integer)

  read("SELECT a.id, a.kind, a.status, w.user_id AS winner_id, COALESCE(COUNT(b.id), 0) AS total_bids,
    COALESCE(
      ARRAY_REMOVE(ARRAY_AGG(DISTINCT b.user_id ORDER BY b.user_id), w.user_id), ARRAY[]::INT[]
    ) AS participant_ids
  FROM auctions a
  LEFT JOIN bids b ON a.id = b.auction_id
  LEFT JOIN (
    SELECT auction_id, user_id, MAX(value_cents) AS value_cents
    FROM bids
    WHERE auction_id = #{auction_id}
    GROUP BY auction_id, user_id
    ORDER BY value_cents DESC
    LIMIT 1
  ) AS w ON a.id = w.auction_id
  WHERE a.id = #{auction_id}
  GROUP BY a.id, w.user_id")
end

#load_participant_statistics(auction_id, participant_id) ⇒ Object 



235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 235

def load_participant_statistics(auction_id, participant_id)
  raise "Invalid argument" unless auction_id.is_a?(Integer) && participant_id.is_a?(Integer)

  read("SELECT a.id, COUNT(b.id) AS auction_total_bids, MAX(b.value_cents) AS winner_bid,
    date(a.finished_at) as auction_date,
    (SELECT COUNT(*) FROM bids b2
      WHERE b2.auction_id = #{auction_id}
      AND b2.user_id = #{participant_id}
    ) AS winner_total_bids
  FROM auctions a
  LEFT JOIN bids b ON a.id = b.auction_id AND a.id = #{auction_id}
  LEFT JOIN users u ON u.id = b.user_id AND u.id = #{participant_id}
  LEFT JOIN (
    SELECT auction_id, user_id, MAX(value_cents) AS value_cents
    FROM bids
    WHERE auction_id = #{auction_id}
    GROUP BY auction_id, user_id
    ORDER BY value_cents DESC
    LIMIT 1
  ) AS w ON a.id = w.auction_id
  WHERE a.id = #{auction_id}
  GROUP BY a.id")
end

#load_penny_auction_winners_and_participants(auction_id) ⇒ Hash

Retrieves the penny auction winner and other participating bidders for a specified auction.

This method queries the database to fetch the winner based on the latest bid and collects an array of other participants who placed bids in the auction, all except for the winner. The method returns structured data that includes the auction ID, winner’s ID, total number of bids, and a list of participant IDs.

Returns:

  • (Hash) —

    a hash containing details about the auction, including winner and participants:

    • :id [Integer] the ID of the auction

    • :winner_id [Integer] the ID of the winning bidder

    • :total_bids [Integer] the total number of bids placed in the auction

    • :participants [Array<Integer>] an array of user IDs of the other participants, excluding the winner



158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 158

def load_penny_auction_winners_and_participants(auction_id)
  raise "Invalid argument" unless auction_id.is_a?(Integer)

  read("SELECT a.id, a.kind, a.status, w.user_id AS winner_id, COALESCE(COUNT(b.id), 0) AS total_bids,
    COALESCE(
      ARRAY_REMOVE(ARRAY_AGG(DISTINCT b.user_id ORDER BY b.user_id), w.user_id), ARRAY[]::INT[]
    ) AS participant_ids
  FROM auctions a
  LEFT JOIN bids b ON a.id = b.auction_id
  LEFT JOIN (
    SELECT auction_id, user_id
    FROM bids
    WHERE auction_id = #{auction_id}
    ORDER BY bids.created_at DESC
    LIMIT 1
  ) AS w ON a.id = w.auction_id
  WHERE a.id = #{auction_id}
  GROUP BY a.id, w.user_id")
end

#load_standard_auction_winners_and_participants(auction_id) ⇒ Hash

Retrieves the standard auction winner and other participating bidders for a specified auction.

This method queries the database to fetch the winner based on the highest bid and collects an array of other participants who placed bids in the auction, all except for the winner. The method returns structured data that includes the auction ID, winner’s ID, total number of bids, and a list of participant IDs.

Returns:

  • (Hash) —

    a hash containing details about the auction, including winner and participants:

    • :id [Integer] the ID of the auction

    • :winner_id [Integer] the ID of the winning bidder

    • :total_bids [Integer] the total number of bids placed in the auction

    • :participants [Array<Integer>] an array of user IDs of the other participants, excluding the winner



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 125

def load_standard_auction_winners_and_participants(auction_id)
  raise "Invalid argument" unless auction_id.is_a?(Integer)

  read("SELECT a.id, a.kind, a.status, w.user_id AS winner_id, COALESCE(COUNT(b.id), 0) AS total_bids,
    COALESCE(
      ARRAY_REMOVE(ARRAY_AGG(DISTINCT b.user_id ORDER BY b.user_id), w.user_id), ARRAY[]::INT[]
    ) AS participant_ids
  FROM auctions a
  LEFT JOIN bids b ON a.id = b.auction_id
  LEFT JOIN (
    SELECT auction_id, user_id, MAX(value_cents) AS value_cents
    FROM bids
    WHERE auction_id = #{auction_id}
    GROUP BY auction_id, user_id
    ORDER BY value_cents DESC
    LIMIT 1
  ) AS w ON a.id = w.auction_id
  WHERE a.id = #{auction_id}
  GROUP BY a.id, w.user_id")
end

#load_winner_statistics(auction_id, winner_id) ⇒ Object 



211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 211

def load_winner_statistics(auction_id, winner_id)
  raise "Invalid argument" unless auction_id.is_a?(Integer) && winner_id.is_a?(Integer)

  read("SELECT a.id, COUNT(b.id) AS auction_total_bids, MAX(b.value_cents) AS winner_bid,
    date(a.finished_at) as auction_date,
    (SELECT COUNT(*) FROM bids b2
      WHERE b2.auction_id = #{auction_id}
      AND b2.user_id = #{winner_id}
    ) AS winner_total_bids
  FROM auctions a
  LEFT JOIN bids b ON a.id = b.auction_id AND a.id = #{auction_id}
  LEFT JOIN users u ON u.id = b.user_id AND u.id = #{winner_id}
  LEFT JOIN (
    SELECT auction_id, user_id, MAX(value_cents) AS value_cents
    FROM bids
    WHERE auction_id = #{auction_id}
    GROUP BY auction_id, user_id
    ORDER BY value_cents DESC
    LIMIT 1
  ) AS w ON a.id = w.auction_id
  WHERE a.id = #{auction_id}
  GROUP BY a.id")
end