splited_genres_df['year'] = splited_genres_df['title'].str.extract('\((\d+)\)', expand=True)
splited_genres_df['title'] = splited_genres_df['title'].str.extract('(.+)\(\d+\)', expand=True)
splited_genres_df.head()WITH RECURSIVE recursive_categories (name, parent_id) AS (
SELECT name, parent_id FROM categories WHERE name = 'PHP developer'
UNION ALL
SELECT c.name, c.parent_id FROM categories c JOIN categories ON c.id = categories.parent_id
) SELECT * FROM recursive_categories
ORDER BY parent_id ASC;select
coalesce(name, 'total') name,
sum(`value`) as value_sum
from reserv
group by name
with rollup;<?php
$res = DB::table('reserv as r')
->selectRaw('
coalesce(name, "total") name,
sum(`value`) as value_sum
')
->groupBy(DB::raw('name with rollup'))
->get();
print_r($res);INSERT INTO `brand` (`name`) VALUES ('My own brand');
INSERT INTO `category` (`name`, `discount`) VALUES ('My sweet category', 8);
INSERT INTO `producttype` (`name`) VALUES ('My little productype');
INSERT INTO `product` (`brandId`, `categoryId`, `productTypeId`, `price`) VALUES (2, 2, 2, 200.55);
SELECT *
FROM `product` `p`
JOIN `producttype` `pt` ON `pt`.`id` = `p`.`productTypeId`
JOIN `category` `c` ON `c`.`id` = `p`.`categoryId`
JOIN `brand` `b` ON `b`.`id` = `p`.`brandId`
;echo ($osName == 'Mac' || $osName == 'Linux') ? $auto_link[$osName] : $auto_link['Default'];echo in_array($osName,['Mac', 'Linux']) ? $auto_link[$osName] : $auto_link['Default'];$query = "
SELECT
items.*,
GROUP_CONCAT(DISTINCT values_color.value) colors,
GROUP_CONCAT(DISTINCT values_base_color.value) base_colors
FROM items
LEFT JOIN values_color ON values_color.item_id = items.id
LEFT JOIN values_base_color ON values_base_color.item_id = items.id
GROUP BY items.id, items.name
;";
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$items = $stmt->fetchAll(PDO::FETCH_ASSOC);SELECT type, time_from, time_to
FROM manager_orders
WHERE aptid = 262707
AND cancelled_at is null
AND (
time_from <= '2021-12-07 16:12:00' + INTERVAL 30 Minute AND
time_to >= '2021-12-07 16:12:00' - INTERVAL 60 Minute
);SELECT strftime('%d.%m.%Y', `date_registration`) AS `date_registration` FROM `users` WHERE `user_id` = ?SELECT to_char(
to_timestamp(parameters->>'time', 'HH24:MI') - (parameters->>'offset' || ' seconds')::interval,
'HH24:MI'
)
FROM reports;SELECT
ru_name
FROM "tokens"
JOIN regions ON regions.id = any (tokens.regions)
WHERE user_id = 5 ;with reg_ids as (
select unnest(regions) reg_id
from tokens where user_id = 5
) select regions.*
from reg_ids
join regions on regions.id = reg_id;create view subscriptions_status as select
id, user_id, created_at, end_at,
case when end_at > now() then 1 else 0 end active
from subscriptions;
select * from subscriptions_status;SELECT cat.id, cat.name, JSON_ARRAYAGG(
JSON_OBJECT('pro_name',pro.name,'pro_pice',pro.price)
) prod
FROM products as pro
JOIN categories as cat on pro.cat_id = cat.id
GROUP BY cat.id, cat.name
ORDER BY cat.id;echo json_encode(array('status'=>$status'result_code'=>$result_code));echo json_encode(array('status'=>$status, 'result_code'=>$result_code));