Count all items in which the user is a participant in the contest

Question

I am currently working on an auction system and the functionality is fully completed. Now I need to add an invoice to the user profile, which shows the number of elements that the user is currently exposed to.

The system consists of two key tables (in the system, of course, there are additional tables, but these are the only tables associated with this problem):

item_sales :

 +-----+------------------+------------+-------------+---------+ | id | selling_format | duration | list_date | buyer | +-----+------------------+------------+-------------+---------+ 

item_sales_bids :

 +-----+-------------+-----------+---------------+-----------+--------+ | id | sale_item | user_id | current_bid | max_bid | date | +-----+-------------+-----------+---------------+-----------+--------+ 

item_sales_bids . date is the Unix timestamp of the bid time.

I can easily get an account of all bets that this user made with the following request:

  SELECT COUNT(DISTINCT(`item_sales_bids`.`user_id`)) AS `total`, SUM((`sale`.`list_date` + (`sale`.`duration` * 86400)) - UNIX_TIMESTAMP()) AS `endTime` FROM `item_sales_bids` INNER JOIN `item_sales` `sale` ON `item_sales_bids`.`sale_item` = `sale`.`id` WHERE `user_id` = 1 GROUP BY `sale_item` HAVING `endTime` > 0 

What I would like to do is run a query similar to the one above, but including only the entries in which the specified user is the highest price (i.e., the maximum identification record for a given set of items is set to user_id = to our user).

Unfortunately, I do not understand how I can achieve this.

I installed SQLFiddle for help> http://sqlfiddle.com/#!2/b98e4/3

Answer 1

Make a subs request to get the latest bid for all items, and then attach it to item_sales_bids to process only the last items.

Something like that: -

 SELECT COUNT(DISTINCT(item_sales_bids.user_id)) AS total, SUM((sale.list_date + (sale.duration * 86400)) - UNIX_TIMESTAMP()) AS endTime FROM item_sales_bids INNER JOIN item_sales sale ON item_sales_bids.sale_item = sale.id INNER JOIN ( SELECT sale_item, MAX(id) AS LatestBid FROM item_sales_bids GROUP BY sale_item ) Sub1 ON item_sales_bids.sale_item = Sub1.sale_item AND item_sales_bids.id = Sub1.LatestBid WHERE user_id = 1 GROUP BY item_sales_bids.sale_item HAVING endTime > 0 

Answer 2

That should do the trick. Switching between user 1 and 2 will show the desired behavior. In the sampled data, user 1 does not return data (they are not a high price), and user 2 returns one row with the current high bid of 50.

  SELECT COUNT(DISTINCT(`bids`.`user_id`)) AS `total`, `highbids`.`sale_item`, `highbids`.`maxBid`, SUM((`sale`.`list_date` + (`sale`.`duration` * 86400)) - UNIX_TIMESTAMP()) AS `endTime` FROM `item_sales_bids` `bids` INNER JOIN `item_sales` `sale` ON `bids`.`sale_item` = `sale`.`id` INNER JOIN (SELECT MAX(`current_bid`) AS `maxBid`, `sale_item`, `user_id` FROM `item_sales_bids` GROUP BY `sale_item`, `user_id` ORDER BY `current_bid` DESC LIMIT 1,1) AS `highbids` ON `bids`.`user_id` = `highbids`.`user_id` WHERE `bids`.`user_id` = 2 GROUP BY `bids`.`sale_item` HAVING `endTime` > 0