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}) ⇒ Array<Hash>

Retrieves a paginated list of auctions along with related information. By default, it retrieves the first page with 10 auctions per page and includes information for the top 3 bidders.

Examples:

Retrieve the first page of auctions with 10 per page and include information for the top 3 bidders

all_auctions = all

Retrieve the second page of auctions with 5 per page and include information for the top 5 bidders

all_auctions = all(2, 5, { bidders_count: 5 })

Retrieve the third page of auctions with 15 per page and include information for the top 2 bidders

all_auctions = all(3, 15, { bidders_count: 2 })

Parameters:

  • page (Integer) (defaults to: 1) —

    The page number to retrieve (default is 1).

  • per_page (Integer) (defaults to: 10) —

    The number of auctions per page (default is 10).

  • options (Hash) (defaults to: {bidders_count: 3}) —

    Additional options for customization (default is 3).

Options Hash (options):

  • :bidders_count (Integer) —

    The number of top bidders to include in the result (default is 3).

Returns:

  • (Array<Hash>) —

    An array of hashes representing the auctions and related information.

Raises:

  • (RuntimeError) —

    if either ‘page` or `per_page` argument is not an integer.



61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 61

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)

  sql = <<-SQL
    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}
  SQL

  read(sql)
end

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

Retrieves detailed information about a specific auction.

Examples:

Retrieve information for auction with ID 123

auction_info = info(123)

Retrieve information for auction with ID 456 and include information for the top 5 bidders

auction_info = info(456, { bidders_count: 5 })

Retrieve information for auction with ID 789 and include information for the top 2 bidders

auction_info = info(789, { bidders_count: 2 })

Parameters:

  • auction_id (Integer) —

    The ID of the auction to retrieve information for.

  • options (Hash) (defaults to: {bidders_count: 3}) —

    Additional options for customization (default is 3).

Options Hash (options):

  • :bidders_count (Integer) —

    The number of top bidders to include in the result (default is 3).

Returns:

  • (Hash) —

    A hash representing the auction and related information.

Raises:

  • (RuntimeError) —

    if ‘auction_id` argument is not an integer.



117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 117

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

  sql = <<-SQL
    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
  SQL

  read(sql)
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



239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 239

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

  sql = <<-SQL
    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
  SQL

  read(sql)
end

#load_participant_statistics(auction_id, participant_id) ⇒ Hash

Loads statistics for a participant in a specific auction.

Examples:

Load statistics for the participant with ID 456 in auction with ID 123

participant_stats = load_participant_statistics(123, 456)

Parameters:

  • auction_id (Integer) —

    The ID of the auction to load statistics for.

  • participant_id (Integer) —

    The ID of the participant to load statistics for.

Returns:

  • (Hash) —

    A hash representing the statistics for the participant in the auction.

Raises:

  • (RuntimeError) —

    if either ‘auction_id` or `participant_id` arguments are not integers.



312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 312

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

  sql = <<-SQL
    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
  SQL

  read(sql)
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



203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 203

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

  sql = <<-SQL
    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
  SQL

  read(sql)
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



166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 166

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

  sql = <<-SQL
    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
  SQL

  read(sql)
end

#load_winner_statistics(auction_id, winner_id) ⇒ Hash

Loads statistics for the winner of a specific auction.

Examples:

Load statistics for the winner of auction with ID 123 and winner with ID 456

winner_stats = load_winner_statistics(123, 456)

Parameters:

  • auction_id (Integer) —

    The ID of the auction to load statistics for.

  • winner_id (Integer) —

    The ID of the winner to load statistics for.

Returns:

  • (Hash) —

    A hash representing the statistics for the winner of the auction.

Raises:

  • (RuntimeError) —

    if either ‘auction_id` or `winner_id` arguments are not integers.



274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
 
# File 'lib/auction_fun_core/relations/auctions.rb', line 274

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

  sql = <<-SQL
    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
  SQL

  read(sql)
end