SQL query for lots and bids: finding if user has highest bid

I'm building a Laravel auction application. I have a table for lots and a table for bids.

Lots table:

mysql> select id, lot_name, created_at, updated_at from 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 * from 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: one lot can contain many bids (from many users) and each bid belongs to only one lot and one user.

My goal is to create a view that displays whether the user has the highest bid for a given lot or not. I will indicate that in a table with green/red row color (green is if user has the highest bid for that lot).

Ideally I want to achieve the following (so I can create a Laravel query and send it to the view to loop over the array)

+--------+---------+----------------+----------------+
| lot_id | user_id | max(bid_value) | highest value  |  extra columns from the lots table such as 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 get to the following:

mysql> select lot_id, max(bid_value) as 'highest value' from bids group by 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) as user_highest_bid from bids where user_id=2 group by lot_id;
+--------+------------------+
| lot_id | user_highest_bid |
+--------+------------------+
|      1 | 525              |
|      2 | 160              |
|      3 | 300              |
|      4 | 600              |
|      5 | 1000             |
|      6 | 1000             |
|      7 | 160              |
+--------+------------------+

How can I combine this to achieve the table I want, so including the additional lot information and only these lots for which the user has given a bid?

You need two queries here - one for the lot's details and one to aggregate the bids. A neat trick here is to use a case statement for the max bid of a certain user, so you only need one aggregate query.

In this query I'm assuming you only want results where the user actually bid on. If this isn't true, just drop the not null condition in the join:

SELECT lot.*, b.*
FROM   lots
JOIN   (SELECT   lot_id, 
                 2 AS user_id, 
                 MAX(bid_value) AS highest_value
                 MAX(CASE user_id WHEN 2 THEN bid_value END) AS highest_bid
        FROM     bids
        GROUP BY lot_id) b ON lot.lot_id = b.lot_id AND highest_bid IS NOT NULL