mysql - SQL query for lots and bids: finding if user has highest bid -
i'm building laravel auction application. have table lots , table bids.
lots table:
mysql> select id, lot_name, created_at, updated_at lots; +----+----------+---------------------+---------------------+ | id | lot_name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | lot 1 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 2 | lot 2 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 3 | lot 3 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 4 | lot 4 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 5 | lot 5 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 6 | lot 6 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 7 | lot 7 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 8 | lot 8 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 9 | lot 9 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 10 | lot 10 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 11 | lot 11 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 12 | lot 12 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 13 | lot 13 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 14 | lot 14 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 15 | lot 15 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | | 16 | lot 16 | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 | +----+----------+---------------------+---------------------+ and bids table:
mysql> select * bids; +----+--------+---------+-----------+---------------------+---------------------+ | id | lot_id | user_id | bid_value | created_at | updated_at | +----+--------+---------+-----------+---------------------+---------------------+ | 1 | 1 | 2 | 100 | 2016-06-01 11:14:15 | 2016-06-09 14:23:59 | | 2 | 1 | 2 | 150 | 2016-06-01 13:19:02 | 2016-06-09 14:23:59 | | 3 | 1 | 3 | 160 | 2016-06-02 09:02:35 | 2016-06-09 14:23:59 | | 4 | 1 | 2 | 180 | 2016-06-02 12:25:16 | 2016-06-09 14:23:59 | | 5 | 2 | 2 | 100 | 2016-06-01 09:26:17 | 2016-06-09 14:23:59 | | 6 | 2 | 3 | 150 | 2016-06-01 10:14:15 | 2016-06-09 14:23:59 | | 7 | 2 | 2 | 160 | 2016-06-01 11:35:09 | 2016-06-09 14:23:59 | | 8 | 3 | 3 | 180 | 2016-06-04 11:14:15 | 2016-06-09 14:23:59 | | 9 | 1 | 3 | 200 | 2016-06-03 19:29:56 | 2016-06-09 14:23:59 | | 10 | 3 | 2 | 250 | 2016-06-04 23:14:15 | 2016-06-09 14:23:59 | | 11 | 4 | 2 | 500 | 2016-06-03 09:16:39 | 2016-06-09 14:23:59 | | 12 | 1 | 3 | 230 | 2016-06-01 11:14:15 | 2016-06-09 14:23:59 | | 13 | 1 | 2 | 250 | 2016-06-01 11:14:15 | 2016-06-09 14:23:59 | | 14 | 3 | 2 | 300 | 2016-06-05 02:14:15 | 2016-06-09 14:23:59 | | 15 | 2 | 3 | 180 | 2016-06-01 20:44:15 | 2016-06-09 14:23:59 | | 16 | 4 | 2 | 600 | 2016-06-04 08:59:15 | 2016-06-09 14:23:59 | | 17 | 1 | 3 | 300 | 2016-06-03 17:20:30 | 2016-06-09 14:23:59 | | 18 | 1 | 3 | 350 | 2016-06-04 18:04:55 | 2016-06-09 14:23:59 | | 19 | 7 | 2 | 160 | 2016-06-02 22:24:02 | 2016-06-09 14:23:59 | | 20 | 1 | 3 | 400 | 2016-06-05 07:00:27 | 2016-06-09 14:23:59 | | 21 | 8 | 3 | 100 | 2016-06-02 11:58:29 | 2016-06-09 14:23:59 | | 22 | 1 | 3 | 450 | 2016-06-05 10:09:06 | 2016-06-09 14:23:59 | | 23 | 1 | 2 | 480 | 2016-06-05 14:12:15 | 2016-06-09 14:23:59 | | 24 | 5 | 2 | 1000 | 2016-06-02 13:38:34 | 2016-06-09 14:23:59 | | 25 | 1 | 2 | 525 | 2016-06-05 20:39:29 | 2016-06-09 14:23:59 | | 26 | 1 | 3 | 600 | 2016-06-05 23:59:50 | 2016-06-09 14:23:59 | | 27 | 6 | 2 | 1000 | 2016-06-01 07:00:02 | 2016-06-09 14:23:59 | | 28 | 6 | 3 | 1200 | 2016-06-04 23:14:56 | 2016-06-09 14:23:59 | +----+--------+---------+-----------+---------------------+---------------------+ relation lot-bid: 1 lot can contain many bids (from many users) , each bid belongs 1 lot , 1 user.
my goal create view displays whether user has highest bid given lot or not. indicate in table green/red row color (green if user has highest bid lot).
ideally want achieve following (so can create laravel query , send view loop on array)
+--------+---------+----------------+----------------+ | lot_id | user_id | max(bid_value) | highest value | columns lots table such created_at, updated_at +--------+---------+----------------+----------------+ | 1 | 2 | 525 | 600 | .... | 2 | 2 | 160 | 180 | .... | 3 | 2 | 300 | 300 | .... | 4 | 2 | 600 | 600 | .... | 5 | 2 | 1000 | 1000 | .... | 6 | 2 | 1000 | 1200 | .... | 7 | 2 | 160 | 160 | .... +--------+--------------------------+----------------+ i following:
mysql> select lot_id, max(bid_value) 'highest value' bids group lot_id; +--------+---------------+ | lot_id | highest value | +--------+---------------+ | 1 | 600 | | 2 | 180 | | 3 | 300 | | 4 | 600 | | 5 | 1000 | | 6 | 1200 | | 7 | 160 | | 8 | 100 | +--------+---------------+ and to:
mysql> select lot_id, max(bid_value) user_highest_bid bids user_id=2 group lot_id; +--------+------------------+ | lot_id | user_highest_bid | +--------+------------------+ | 1 | 525 | | 2 | 160 | | 3 | 300 | | 4 | 600 | | 5 | 1000 | | 6 | 1000 | | 7 | 160 | +--------+------------------+ how can combine achieve table want, including additional lot information , these lots user has given bid?
---- edit ---- adding correct answer mureinik (with minor changes):
mysql> select lots.*, b.* -> lots -> join (select lot_id, -> 2 user_id, -> max(bid_value) highest_value, -> max(case user_id when 2 bid_value end) highest_bid -> bids -> group lot_id) b on lots.id = b.lot_id , highest_bid not null;
you need 2 queries here - 1 lot's details , 1 aggregate bids. neat trick here use case statement max bid of user, need 1 aggregate query.
in query i'm assuming want results user bid on. if isn't true, drop not null condition in join:
select lot.*, b.* lots join (select lot_id, 2 user_id, max(bid_value) highest_value, max(case user_id when 2 bid_value end) highest_bid bids group lot_id) b on lot.lot_id = b.lot_id , highest_bid not null
Comments
Post a Comment