In my Auction application, I have 3 primary tables, Auctions, Bids, and User. I'm trying to build a MongoDB query where I can get the highest bid of each user on each auction (if they placed a bid), also I want to have a column that says the status of the user on that particular auction. I'm able to get all columns except the status column which is kinda complicated.
Logic for status column
In my Auction application, I have 3 main tables, Auction, Bids and Users. Table structure shown below
Users
| userID | fullName | role |
|---|---|---|
| User1 | xxxx | bidder |
| User2 | xxxx | bidder |
| User3 | xxxx | bidder |
Auctions
| auctionID | startDate | endDate | startingPrice |
|---|---|---|---|
| 10001 | 2023-09-20T12:37:00Z | 2023-09-21T12:37:00Z | 100 |
| 10002 | 2023-09-22T12:37:00Z | 2023-09-22T12:37:00Z | 2000 |
| 10003 | 2023-09-22T12:37:00Z | 2023-09-25T12:37:00Z | 800 |
| 10004 | 2023-09-22T12:37:00Z | 2023-09-26T12:37:00Z | 3000 |
Bids
| bidID | auctionID | bidderID | bidAmount | bidDate |
|---|---|---|---|---|
| 101 | 10001 | User1 | 120 | 2023-09-20T13:33:44Z |
| 102 | 10001 | User2 | 140 | 2023-09-20T13:33:44Z |
| 103 | 10001 | User1 | 160 | 2023-09-20T13:33:44Z |
| 104 | 10001 | User2 | 200 | 2023-09-20T13:33:44Z |
| 105 | 10001 | User1 | 240 | 2023-09-20T13:33:44Z |
| 106 | 10002 | User3 | 2200 | 2023-09-20T13:33:44Z |
| 107 | 10002 | User2 | 2400 | 2023-09-20T13:33:44Z |
| 108 | 10002 | User1 | 2800 | 2023-09-20T13:33:44Z |
| 109 | 10002 | User2 | 3200 | 2023-09-20T13:33:44Z |
| 110 | 10002 | User3 | 3400 | 2023-09-20T13:33:44Z |
| 111 | 10003 | User1 | 900 | 2023-09-20T13:33:44Z |
| 112 | 10003 | User2 | 1200 | 2023-09-20T13:33:44Z |
| 113 | 10003 | User3 | 1600 | 2023-09-20T13:33:44Z |
| 114 | 10003 | User2 | 1800 | 2023-09-20T13:33:44Z |
Expected Result
| userID | auctionID | maxBidAmount | status |
|---|---|---|---|
| User1 | 1001 | 240 | You won the auction |
| User1 | 1002 | 2800 | You lost the auction |
| User1 | 1003 | 900 | You have been outbid |
| User2 | 1001 | 200 | You lost the auction |
| User2 | 1002 | 3200 | You lost the auction |
| User2 | 1003 | 1800 | You are the highest bidder |
| User3 | 1002 | 3400 | You won the auction |
| User3 | 1003 | 1600 | You have been outbid |
Here is my mongodb query which gives wrong result in status column
this.bidModel.aggregate([
{
$sort: {
auction: 1,
bidAmount: -1
}
},
{
$group: {
_id: {
bidder: "$bidder",
auction: "$auction"
},
highestBid: { $first: "$$ROOT" }
}
},
{ $lookup: { from: 'users', localField: '_id.bidder', foreignField: '_id', as: 'bidderInfo' } },
{ $lookup: { from: 'auctions', localField: '_id.auction', foreignField: '_id', as: 'auctionInfo' } },
{ $unwind: '$bidderInfo' },
{ $unwind: '$auctionInfo' },
{
$project: {
_id: 0, // Exclude the _id field
bidID: "$highestBid.bidID",
bidAmount: "$highestBid.bidAmount",
bidDate: "$highestBid.bidDate",
fullName: "$bidderInfo.fullName",
bidder: "$bidderInfo._id",
auctionID: "$auctionInfo._id",
auction: "$auctionInfo.title",
endDate: "$auctionInfo.endDate"
}
},
{
$addFields: {
status: {
$cond: [
{
$and: [
{ $eq: ["$bidder", "$_id.bidder"] }, // User is highest bidder
{ $gt: ["$endDate", new Date()] } // Auction has not ended
]
},
"You are the highest bidder",
{
$cond: [
{
$and: [
{ $ne: ["$bidder", "$_id.bidder"] }, // User is outbid
{ $gt: ["$endDate", new Date()] } // Auction has not ended
]
},
"You have been outbid",
{
$cond: [
{ $eq: ["$bidder", "$_id.bidder"] }, // User is highest bidder
"You won the auction",
"You lost the auction" // Auction ended, and user is not the highest bidder
]
}
]
}
]
}
}
},
// Sort the result by bidder
{
$sort: {
userID: 1 // Sort by bidder in ascending order
}
}
]);
Any help will be appreciated!
One option is to use $setWindowFields and $switch:
$group by bidderID and auctionID$lookup to check if the auction is over (that is all the data we need)$setWindowFields to get the highest bid per auction$set to format the data$project to calculate the status$sortdb.bids.aggregate([
{$group: {
_id: {bidder: "$bidderID", auction: "$auctionID"},
myHighestBid: {$max: "$bidAmount"}
}},
{$lookup: {
from: "auctions",
localField: "_id.auction",
foreignField: "auctionID",
as: "isEnded",
pipeline: [{$project: {isEnded: {$lt: ["$endDate", "$$NOW"]}}}]
}},
{$setWindowFields: {
partitionBy: "$_id.auction",
sortBy: {myHighestBid: -1},
output: {maxBidAmount: {
$max: "$myHighestBid",
window: {documents: [ "unbounded", "current"]}}
}
}},
{$set: {isEnded: {$first: "$isEnded.isEnded"}}},
{$project: {
_id: 0,
userID: "$_id.bidder",
auctionID: "$_id.auction",
maxBidAmount: "$myHighestBid",
status: {$switch: {
branches: [
{
case: {$and: [{$eq: ["$myHighestBid", "$maxBidAmount"]},
"$isEnded"]},
then: "You won the auction"
},
{
case: {$and: [{$eq: ["$myHighestBid", "$maxBidAmount"]},
{$not: "$isEnded"}]},
then: "You are the highest bidder"
},
{
case: {$and: [{$lt: ["$myHighestBid", "$maxBidAmount"]},
"$isEnded"]},
then: "You lost the auction"
}
],
default: "You have been outbid"
}}
}},
{$sort: {userID: 1, auctionID: 1}}
])
See how it works on the playground example