By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601498 fiddles created (47968 in the last week).
SET SQL_MODE='ALLOW_INVALID_DATES';
CREATE TABLE `wtb_tokens` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'User name',
`orderid` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '-1 for preview channel tokens',
`token` varchar(128) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Hashed tokens',
`token_clean` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`is_expired` tinyint(1) NOT NULL DEFAULT '0',
`disconnect_time` float NOT NULL DEFAULT '0',
`encoder` tinyint(1) NOT NULL DEFAULT '0',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expires` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`service_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'to link the token with a service for preview channels',
`video_id` int(11) NOT NULL COMMENT 'to link the token with a a video for preview',
PRIMARY KEY (`id`),
UNIQUE KEY `index_token` (`token`),
KEY `index_orderid` (`orderid`),
KEY `index_username` (`username`)
);
✓
✓
hidden batch(es)
CREATE TABLE `wtb_orders` (
`orderid` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`username` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`msisdn` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`order_type` varchar(999) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Subscription',
`bundle_id` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`service_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`object_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`pkg_id_fk` int(11) DEFAULT NULL,
`app_id` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`balance` float NOT NULL,
`cost` float NOT NULL DEFAULT '0',
`is_paid` int(1) NOT NULL DEFAULT '0',
`is_renew` tinyint(1) NOT NULL DEFAULT '1',
`is_test` tinyint(1) NOT NULL DEFAULT '0',
`duration` int(11) NOT NULL DEFAULT '0',
`extended_duration` int(11) NOT NULL DEFAULT '0',
`disconnect_time` int(11) NOT NULL DEFAULT '0',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`paid_on` timestamp NULL DEFAULT NULL,
`was_renewed` tinyint(1) NOT NULL DEFAULT '0',
`renew_orderid` varchar(50) DEFAULT NULL,
`renewed_at` varchar (50) NOT NULL,
`generated_by` varchar(100),
`gift_to` varchar(125),
`price_id` int(11) NOT NULL DEFAULT '0',
`renewal_price_id` int(11) NOT NULL DEFAULT '0',
`renewal_pkg_id` int(11) NOT NULL DEFAULT '0',
`bypass` tinyint(1) NOT NULL DEFAULT '0',
`bypass_reason` text,
`reset_access` tinyint(1) NOT NULL DEFAULT '0',
`extend_access` tinyint(1) NOT NULL DEFAULT '0',
`is_revoked` tinyint(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`orderid`),
KEY `service_id_index` (`service_id`),
KEY `index_username` (`username`),
KEY `wtb_orders_paid_index` (`is_paid`,`bundle_id`),
KEY `prices_bundle_id_index` (`bundle_id`)
);
SELECT * FROM (
SELECT
wtb_orders.orderid,
wtb_orders.duration,
wtb_orders.is_renew,
wtb_orders.bundle_id,
wtb_orders.service_id,
wtb_orders.object_name,
wtb_orders.pkg_id_fk,
expires,
wtb_tokens.disconnect_time
FROM wtb_orders
JOIN wtb_tokens ON wtb_orders.orderid=wtb_tokens.orderid
WHERE
wtb_orders.username = "96190000002" AND
(
(
((expires + INTERVAL 5 DAY) >= NOW())
OR
(expires=0 AND wtb_tokens.orderid IS NOT NULL)
) OR
expires=0
)
ORDER by expires DESC LIMIT 1000000
) as tmp
GROUP BY
tmp.pkg_id_fk,
tmp.bundle_id,
tmp.service_id
UNION
(
SELECT ord.orderid,ord.duration,ord.is_renew,ord.bundle_id,ord.service_id,ord.object_name,ord.pkg_id_fk,tk.expires,tk.disconnect_time FROM wtb_orders AS ord
JOIN wtb_tokens AS tk ON ord.orderid=tk.orderid
WHERE tk.username= "96190000002"
AND (((tk.expires >= NOW())
OR (tk.expires=0 AND tk.orderid IS NOT NULL))
OR tk.expires=0)
)
orderid
duration
is_renew
bundle_id
service_id
object_name
pkg_id_fk
expires
disconnect_time
5ddba0231cffe
0
1
55dc5b012ba2d100
a57daf281601be78ef94989b7e7d5752.mp4
0000-00-00 00:00:00
0
5e5f5f6e16af3
720
1
5acca06f5b42b24
f06f1d51bef3720fec0b97f991fec5e8.mp4
2020-04-03 09:57:34
0
5e5f5a794ec18
0
1
5ad46e5742c0413
b36b096f8b3bb05dbfcf0c2c971dc97a.mp4
0000-00-00 00:00:00
0
5e732c756cb98
36
1
5ad46fd59f47884
403cb6ea547f862eaeb3a7ad43bfa4a2.mp4
2020-03-20 22:25:25
0
5e4dafe228a6d
720
1
5b8656b5d30b965
2020-03-21 00:00:02
0
5e4dafe26da8c
720
0
5b8659c7754f175
2020-03-21 00:00:02
0
5e4dafe318f34
720
0
5b865ba46b44685
2020-03-21 00:00:03
0
5e4dafe1ec6d2
720
0
5b865c11927b360
2020-03-21 00:00:02
0
5e4dafe2bb0fd
720
1
5b865db83749c22
2020-03-21 00:00:02
0
5e7329f182f22
720
1
26
2020-04-18 10:14:41
0
5e732c8e55dfd
720
1
27
2020-04-18 10:25:50
0
5e739d89621b7
24
1
35
2020-03-20 18:27:53
0
5e5f5a434fe83
0
1
55dc5b012ba2d100
e4a57106f6167d568b883c6162c14953.mp4
0000-00-00 00:00:00
0
5e5f5a69df6ae
0
1
55dc5b012ba2d100
ddb9f88c2a1795116ba88097709886f9.mp4
0000-00-00 00:00:00
0
…
hidden batch(es)
SELECT
*
FROM
(SELECT
wtb_orders_orderid,
wtb_orders_duration,
wtb_orders_is_renew,
wtb_orders_bundle_id,
wtb_orders_service_id,
wtb_orders_object_name,
wtb_orders_pkg_id_fk,
wtb_tokens_expires,
wtb_tokens_disconn_time
FROM
((SELECT
wtb_orders.orderid AS wtb_orders_orderid,
wtb_orders.duration AS wtb_orders_duration,
wtb_orders.is_renew AS wtb_orders_is_renew,
wtb_orders.bundle_id AS wtb_orders_bundle_id,
wtb_orders.service_id AS wtb_orders_service_id,
wtb_orders.object_name AS wtb_orders_object_name,
wtb_orders.pkg_id_fk AS wtb_orders_pkg_id_fk,
wtb_tokens.expires AS wtb_tokens_expires,
wtb_tokens.disconnect_time AS wtb_tokens_disconn_time
FROM
wtb_orders
JOIN
wtb_tokens
ON wtb_orders.orderid = wtb_tokens.orderid
WHERE
wtb_orders.username = '96190000002'
AND (
wtb_tokens.expires = 0
)
ORDER BY
wtb_tokens.expires DESC LIMIT 1000000)
UNION
DISTINCT (SELECT
wtb_orders.orderid AS wtb_orders_orderid,
wtb_orders.duration AS wtb_orders_duration,
wtb_orders.is_renew AS wtb_orders_is_renew,
wtb_orders.bundle_id AS wtb_orders_bundle_id,
wtb_orders.service_id AS wtb_orders_service_id,
wtb_orders.object_name AS wtb_orders_object_name,
wtb_orders.pkg_id_fk AS wtb_orders_pkg_id_fk,
wtb_tokens.expires AS wtb_tokens_expires,
wtb_tokens.disconnect_time AS wtb_tokens_disconn_time
FROM
wtb_orders
JOIN
wtb_tokens
ON wtb_orders.orderid = wtb_tokens.orderid
WHERE
wtb_orders.username = '96190000002'
AND (((wtb_tokens.expires = 0
AND wtb_tokens.orderid IS NOT NULL)))
ORDER BY
wtb_tokens.expires DESC LIMIT 1000000)
UNION
DISTINCT (SELECT
wtb_orders.orderid AS wtb_orders_orderid,
wtb_orders.duration AS wtb_orders_duration,
wtb_orders.is_renew AS wtb_orders_is_renew,
wtb_orders.bundle_id AS wtb_orders_bundle_id,
wtb_orders.service_id AS wtb_orders_service_id,
wtb_orders.object_name AS wtb_orders_object_name,
wtb_orders.pkg_id_fk AS wtb_orders_pkg_id_fk,
wtb_tokens.expires AS wtb_tokens_expires,
wtb_tokens.disconnect_time AS wtb_tokens_disconn_time
FROM
wtb_orders
JOIN
wtb_tokens
ON wtb_orders.orderid = wtb_tokens.orderid
WHERE
wtb_orders.username = '96190000002'
AND ((((wtb_tokens.expires + INTERVAL 5 DAY) >= NOW())))
ORDER BY
wtb_tokens.expires DESC LIMIT 1000000)
) AS union1
ORDER BY
union1.wtb_tokens_expires DESC LIMIT 1000000) AS tmp
GROUP BY
tmp.pkg_id_fk,
tmp.bundle_id,
tmp.service_id
UNION
(
SELECT
ord.orderid,
ord.duration,
ord.is_renew,
ord.bundle_id,
ord.service_id,
ord.object_name,
ord.pkg_id_fk,
tk.expires,
tk.disconnect_time
FROM
wtb_orders AS ord
JOIN
wtb_tokens AS tk
ON ord.orderid = tk.orderid
WHERE
tk.username = '96190000002'
AND (
tk.expires = 0
)
)
UNION
DISTINCT (SELECT
ord.orderid,
ord.duration,
ord.is_renew,
ord.bundle_id,
ord.service_id,
ord.object_name,
ord.pkg_id_fk,
tk.expires,
tk.disconnect_time
FROM
wtb_orders AS ord
JOIN
wtb_tokens AS tk
ON ord.orderid = tk.orderid
WHERE
tk.username = '96190000002'
AND (((tk.expires = 0
AND tk.orderid IS NOT NULL))))
UNION
DISTINCT (SELECT
ord.orderid,
ord.duration,
ord.is_renew,
ord.bundle_id,
ord.service_id,
ord.object_name,
ord.pkg_id_fk,
tk.expires,
tk.disconnect_time
FROM
wtb_orders AS ord
JOIN
wtb_tokens AS tk
ON ord.orderid = tk.orderid
WHERE
tk.username = '96190000002'
AND (((tk.expires >= NOW()))))
Unknown column 'tmp.pkg_id_fk' in 'group statement'
