I have a table as below:
CREATE TABLE IF NOT EXISTS `room_players`
(`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
,`player_id` int(11) NOT NULL
,`room_id` tinyint(1) NOT NULL
,`dealer` tinyint(1) NOT NULL
);
INSERT INTO room_players (`player_id`, `room_id`, `dealer`) VALUES
(1, 1, '0'),
(2, 1, '0'),
(3, 1, '0'),
(4, 1, '1'),
(5, 1, '0');
I need to get row after WHERE dealer='1' AND room_id='1'; in this case, it should return player_id = 5.
Now, when dealer = 1 for player_id = 5, then it should select the first player_id of the current room_id.
I tried:
SELECT *
FROM room_players
WHERE room_id='1'
AND id IN ( SELECT ID 1
FROM room_players
WHERE room_id='1'
AND dealer != 1
)
Selecting the next row works with this query but, when we got to the last player in line, it will return nothing.
I could run 2 different queries to get it work, but I would like to use an eloquent single query.
CodePudding user response：
Using window functions we can achieve this in 1 query pretty easily:
- first_value() asending id
- first_value() descending id
- lead()
- case expression to figure out when to display next dealer value.
- Just note: elegance of 1 query may make maintenance more complex.
- it's not always better to build 1 sometimes two IS simplier.
Demo you can adjust dealer to wherever you want and it will return the next dealer player number on the dealer number line. you didn't define expected results so I am unsure what you result is to look like.
SELECT *
, case WHEN dealer=1 and player_id = first_value(player_id) over (partition by room_id order by id desc)
THEN first_value(player_id) over (partition by room_id order by id)
WHEN dealer=1
THEN lead(player_id) over (partition by room_id order by id) end as NextDealer
From room_players
Giving us:
---- ----------- --------- -------- ------------
| id | player_id | room_id | dealer | NextDealer |
---- ----------- --------- -------- ------------
| 1 | 1 | 1 | 0 | |
| 2 | 2 | 1 | 1 | 3 |
| 3 | 3 | 1 | 0 | |
| 4 | 4 | 1 | 0 | |
| 5 | 5 | 1 | 0 | |
---- ----------- --------- -------- ------------
or
---- ----------- --------- -------- ------------
| id | player_id | room_id | dealer | NextDealer |
---- ----------- --------- -------- ------------
| 1 | 1 | 1 | 0 | |
| 2 | 2 | 1 | 0 | |
| 3 | 3 | 1 | 0 | |
| 4 | 4 | 1 | 0 | |
| 5 | 5 | 1 | 1 | 1 |
---- ----------- --------- -------- ------------
depending on who is dealer..
DB Fiddle expanded to show what's happening with case expression & show how to get dealer/room. Yes I had to use a CTE because the window functions can't have the data limited; it first must have the data materialized. But, if you create the CTE as a view and you're simply passing in the room to get the next dealer this works fine imo.
We can use:
WITH CTE AS (
SELECT *,
case WHEN dealer=1 and player_id = first_value(player_id) over (partition by room_id order by id desc)
THEN first_value(player_id) over (partition by room_id order by id)
WHEN dealer=1
THEN lead(player_id) over (partition by room_id order by id) end NextDealer
From room_players)
SELECT room_id, NextDealer FROM CTE WHERE NextDealer is not null
to get data by room and next dealer giving us: (I added some additional test data to make sure certain edge cases were working as expected)
--------- ------------
| room_id | NextDealer |
--------- ------------
| 1 | 7 |
| 2 | 99 |
--------- ------------
With Date of:
---- ----------- --------- --------
| id | player_id | room_id | dealer |
---- ----------- --------- --------
| 1 | 1 | 1 | 0 |
| 2 | 2 | 1 | 0 |
| 3 | 3 | 1 | 0 |
| 4 | 4 | 1 | 1 |
| 5 | 7 | 1 | 0 |
| 6 | 99 | 2 | 0 |
| 7 | 14 | 2 | 0 |
| 8 | 22 | 2 | 0 |
| 9 | 77 | 2 | 1 |
| 10 | 15 | 2 | 0 |
---- ----------- --------- --------
I get:
---- ----------- --------- -------- ---- ---- ---- ------------
| id | player_id | room_id | dealer | a | b | c | NextDealer |
---- ----------- --------- -------- ---- ---- ---- ------------
| 1 | 1 | 1 | 0 | 7 | 1 | 2 | |
| 2 | 2 | 1 | 0 | 7 | 1 | 3 | |
| 3 | 3 | 1 | 0 | 7 | 1 | 4 | |
| 4 | 4 | 1 | 1 | 7 | 1 | 7 | 7 |
| 5 | 7 | 1 | 0 | 7 | 1 | | |
| 6 | 99 | 2 | 0 | 15 | 99 | 14 | |
| 7 | 14 | 2 | 0 | 15 | 99 | 22 | |
| 8 | 22 | 2 | 0 | 15 | 99 | 77 | |
| 9 | 77 | 2 | 1 | 15 | 99 | 15 | 15 |
| 10 | 15 | 2 | 0 | 15 | 99 | | |
---- ----------- --------- -------- ---- ---- ---- ------------
CodePudding user response：
I went with slightly different approach, i got to solve it with one simple query, but did some coding with PHP also. Its not the eloquant i wanted, but it works for me.
function dealer($room_id) {
global $con;
$array = array();
$i = 0;
$cd = null;
$query = mysqli_query($con, "SELECT * FROM room_players WHERE room_id='".$room_id."'");
while($ft = mysqli_fetch_array($query)) {
$array[] = array("user_id" => $ft['user_id']);
if($ft['dealer'] == 1) {
$cd = $i;
}
$i ;
}
if(!is_null($cd)) {
if($array[$cd 1]) {
$next = $array[$cd 1]['user_id'];
}else{
$next = $array[0]['user_id'];
}
}else{
$next = $array[array_rand($array, 1)]['user_id'];
}
return $next;
}
echo "Next dealer is: ". dealer(1);