Gists
On gists
MySql - advanced
Non Equi Join
non-equi.sql #
-- Zaměstnanci
+------+-------+--------------+
| ID | Jméno | Úroveň |
+------+-------+--------------+
| 1 | John | Expert |
| 2 | Mary | Intermediate |
| 3 | Peter | Beginner |
+------+-------+--------------+
-- Zakázky
+------+-------------------+
| ID | Požadovaná_úroveň |
+------+-------------------+
| 1 | Intermediate |
| 2 | Beginner |
| 3 | Expert |
+------+-------------------+
SELECT Zaměstnanci.ID, Zaměstnanci.Jméno, Zaměstnanci.Úroveň, Zakázky.Požadovaná_úroveň
FROM Zaměstnanci
JOIN Zakázky ON Zaměstnanci.Úroveň > Zakázky.Požadovaná_úroveň;
-- Výsledek,
+------+-------+--------------+-------------------+
| ID | Jméno | Úroveň | Požadovaná_úroveň |
+------+-------+--------------+-------------------+
| 1 | John | Expert | Beginner |
| 2 | Mary | Intermediate | Beginner |
| 2 | Mary | Intermediate | Expert |
+------+-------+--------------+-------------------+
On gists
MySql
MySql tricks
MySql - advanced
Advanced CTE - Tree
tree.sql #
https://akki.ca/blog/mysql-adjacency-list-model-for-hierarchical-data-using-cte/
CREATE TABLE category (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
parent_id int(10) UNSIGNED DEFAULT NULL,
is_active int(1) UNSIGNED DEFAULT 1,
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES category (id)
ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO category (name, parent_id) VALUES ("Electronics", null);
INSERT INTO category (name, parent_id) VALUES ("TV", 1);
INSERT INTO category (name, parent_id) VALUES ("Smart", 2);
INSERT INTO category (name, parent_id) VALUES ("4K Ultra HD", 2);
INSERT INTO category (name, parent_id) VALUES ("Curved", 2);
INSERT INTO category (name, parent_id) VALUES ("Camera", 1);
INSERT INTO category (name, parent_id) VALUES ("Computer", 1);
INSERT INTO category (name, parent_id) VALUES ("Desktop", 7);
INSERT INTO category (name, parent_id) VALUES ("Laptops", 7);
INSERT INTO category (name, parent_id) VALUES ("Work", 9);
INSERT INTO category (name, parent_id) VALUES ("Travel", 9);
INSERT INTO category (name, parent_id) VALUES ("All Around", 9);
INSERT INTO category (name, parent_id) VALUES ("Gaming", 9);
INSERT INTO category (name, parent_id) VALUES ("Tablet", 7);
/* Tree */
WITH RECURSIVE shoppingCategories AS
(
SELECT id, name, parent_id, 1 AS depth, name AS path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, sc.depth + 1, CONCAT(sc.path, ' > ', c.name)
FROM shoppingCategories AS sc
JOIN category AS c ON sc.id = c.parent_id
)
SELECT * FROM shoppingCategories;
+------+-------------+-----------+-------+-----------------------------------------------+
| id | name | parent_id | depth | path |
+------+-------------+-----------+-------+-----------------------------------------------+
| 1 | Electronics | NULL | 1 | Electronics |
| 2 | TV | 1 | 2 | Electronics > TV |
| 6 | Camera | 1 | 2 | Electronics > Camera |
| 7 | Computer | 1 | 2 | Electronics > Computer |
| 3 | Smart | 2 | 3 | Electronics > TV > Smart |
| 4 | 4K Ultra HD | 2 | 3 | Electronics > TV > 4K Ultra HD |
| 5 | Curved | 2 | 3 | Electronics > TV > Curved |
| 8 | Desktop | 7 | 3 | Electronics > Computer > Desktop |
| 9 | Laptops | 7 | 3 | Electronics > Computer > Laptops |
| 14 | Tablet | 7 | 3 | Electronics > Computer > Tablet |
| 10 | Work | 9 | 4 | Electronics > Computer > Laptops > Work |
| 11 | Travel | 9 | 4 | Electronics > Computer > Laptops > Travel |
| 12 | All Around | 9 | 4 | Electronics > Computer > Laptops > All Around |
| 13 | Gaming | 9 | 4 | Electronics > Computer > Laptops > Gaming |
+------+-------------+-----------+-------+-----------------------------------------------+
/* ancestors, tree */
SET @catID = 9;
WITH RECURSIVE
ancestorCategories AS
(
SELECT id, name, parent_id, 1 AS depth
FROM category
WHERE id = @catID
UNION ALL
SELECT c.id, c.name, c.parent_id, ac.depth - 1
FROM ancestorCategories AS ac
JOIN category AS c ON ac.parent_id = c.id
),
ancestorDeptOneCategories AS
(
SELECT id, name, parent_id, 1 AS depth
FROM category
WHERE parent_id IS null
UNION ALL
SELECT c.id, c.name, c.parent_id, adoc.depth + 1
FROM ancestorDeptOneCategories AS adoc
JOIN ancestorCategories ac ON ac.id = adoc.id
JOIN category AS c ON adoc.id = c.parent_id
)
SELECT * FROM ancestorDeptOneCategories
ORDER BY depth;
+------+-------------+-----------+-------+
| id | name | parent_id | depth |
+------+-------------+-----------+-------+
| 1 | Electronics | NULL | 1 |
| 2 | TV | 1 | 2 |
| 6 | Camera | 1 | 2 |
| 7 | Computer | 1 | 2 |
| 8 | Desktop | 7 | 3 |
| 9 | Laptops | 7 | 3 |
| 14 | Tablet | 7 | 3 |
| 10 | Work | 9 | 4 |
| 11 | Travel | 9 | 4 |
| 12 | All Around | 9 | 4 |
| 13 | Gaming | 9 | 4 |
+------+-------------+-----------+-------+
On gists
PHP
Fake visitor u productu ;)
fake-visitor-by-gtp.php #
<?php
session_start();
// Interval v sekundách, po kterém se změní počet prohlížejících
$interval = 10; // Například každých 10 sekund
// Kontrola, zda již byl počet prohlížejících inicializován v session
if (!isset($_SESSION['prohlizeloLidi'])) {
// Pokud ne, vygenerujte falešný počet prohlížejících (např. 65)
$_SESSION['prohlizeloLidi'] = 65;
$_SESSION['lastUpdate'] = time(); // Poslední aktualizace na aktuální čas
}
// Získání počtu prohlížejících z session
$prohlizeloLidi = $_SESSION['prohlizeloLidi'];
// Získání času poslední aktualizace
$lastUpdate = $_SESSION['lastUpdate'];
// Aktuální čas
$currentTime = time();
// Počet sekund od poslední aktualizace
$secondsSinceLastUpdate = $currentTime - $lastUpdate;
// Změna počtu prohlížejících po uplynutí intervalu
if ($secondsSinceLastUpdate >= $interval) {
// Generování náhodného směru změny (-1 pro snížení, 1 pro zvýšení)
$changeDirection = rand(0, 1) === 0 ? -1 : 1;
// Generování náhodného počtu prohlížejících, který se přičte nebo odečte od aktuálního počtu
$changeAmount = rand(1, 5); // Například změna o 1 až 5 prohlížejících
// Nový počet prohlížejících
$prohlizeloLidi += $changeDirection * $changeAmount;
// Ošetření, aby počet neklesl pod nulu
if ($prohlizeloLidi < 0) {
$prohlizeloLidi = 0;
}
// Aktualizace posledního aktualizovaného času
$_SESSION['lastUpdate'] = $currentTime;
}
// Uložení nového počtu prohlížejících do session
$_SESSION['prohlizeloLidi'] = $prohlizeloLidi;
// Vypsání informace
echo "Toto zboží si právě prohlíží $prohlizeloLidi lidí.";
// Ukončení session
session_write_close();
?>
On gists
With recursive - CTE
•
MySql
MySql tricks
MySql - advanced
cte.sql #
WITH RecursiveSequence AS (
SELECT 1 AS number
UNION ALL
SELECT number + 1
FROM RecursiveSequence
WHERE number < 10
)
SELECT * FROM RecursiveSequence;
+--------+
| number |
+--------+
| 1 |
| 2 |
| 3 |
| ... |
| 10 |
+--------+
/*
numbers, days interval
*/
WITH RECURSIVE cte(n, dt) AS (
SELECT 10, '2023-01-01'
UNION ALL
SELECT n + 10, dt + INTERVAL 1 day
FROM cte WHERE n < 1000
)
/*
nebo takto kdyz nechci uvadet za cte(parametry):
WITH RECURSIVE cte AS (
SELECT 10 AS n, '2023-01-01' AS dt
UNION ALL
SELECT n + 10, dt + INTERVAL 1 day
FROM cte WHERE n < 1000
)
*/
SELECT * FROM cte;
/* tree, path, level */
# create the table
CREATE TABLE orgchart(
id INT PRIMARY KEY,
name VARCHAR(20),
role VARCHAR(20),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES orgchart(id));
# insert the rows
INSERT INTO orgchart VALUES(1,'Matthew','CEO',NULL),
(2,'Caroline','CFO',1),(3,'Tom','CTO',1),
(4,'Sam','Treasurer',2),(5,'Ann','Controller',2),
(6,'Anthony','Dev Director',3),(7,'Lousie','Sys Admin',3),
(8,'Travis','Senior DBA',3),(9,'John','Developer',6),
(10,'Jennifer','Developer',6),(11,'Maria','Junior DBA',8);
# let's see the table, The CEO has no manager, so the manager_id is set to NULL
SELECT * FROM orgchat;
+----+----------+--------------+------------+
| id | name | role | manager_id |
+----+----------+--------------+------------+
| 1 | Matthew | CEO | NULL |
| 2 | Caroline | CFO | 1 |
| 3 | Tom | CTO | 1 |
| 4 | Sam | Treasurer | 2 |
| 5 | Ann | Controller | 2 |
| 6 | Anthony | Dev Director | 3 |
| 7 | Lousie | Sys Admin | 3 |
| 8 | Travis | Senior DBA | 3 |
| 9 | John | Developer | 6 |
| 10 | Jennifer | Developer | 6 |
| 11 | Maria | Junior DBA | 8 |
+----+----------+--------------+------------+
WITH RECURSIVE reporting_chain(id, name, PATH, PATH2, level) AS
(
SELECT
id, name,
CAST(name AS CHAR(100)),
CAST(name AS CHAR(100)),
1
FROM orgchart
WHERE manager_id IS NULL
UNION ALL
SELECT
oc.id,
oc.name,
CONCAT(rc.path,' -> ',oc.name),
CONCAT(oc.name,' -> ', rc.path2),
rc.level+1
FROM reporting_chain rc
JOIN orgchart oc ON rc.id=oc.manager_id
)
SELECT * FROM reporting_chain ORDER BY level;
+------+----------+---------------------------------------+-------+
| id | name | path | level |
+------+----------+---------------------------------------+-------+
| 1 | Matthew | Matthew | 1 |
| 2 | Caroline | Matthew -> Caroline | 2 |
| 3 | Tom | Matthew -> Tom | 2 |
| 4 | Sam | Matthew -> Caroline -> Sam | 3 |
| 5 | Ann | Matthew -> Caroline -> Ann | 3 |
| 6 | Anthony | Matthew -> Tom -> Anthony | 3 |
| 7 | Lousie | Matthew -> Tom -> Lousie | 3 |
| 8 | Travis | Matthew -> Tom -> Travis | 3 |
| 9 | John | Matthew -> Tom -> Anthony -> John | 4 |
| 10 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer | 4 |
| 11 | Maria | Matthew -> Tom -> Travis -> Maria | 4 |
+------+----------+---------------------------------------+-------+
/* node paths */
WITH RECURSIVE
cte ( node, path )
AS
( SELECT node, cast ( 1 as char(30) )
FROM bst WHERE parent IS NULL
UNION ALL
SELECT bst.node, CONCAT ( cte.path, '-->', bst.node )
FROM cte JOIN bst ON cte.node = bst.parent
)
SELECT * FROM cte ORDER BY node;
/* next tree */
CREATE TABLE
categories (
id int,
cat_name varchar(100),
parent_category_id int DEFAULT NULL
);
INSERT INTO
categories
VALUES
(1, 'Mens', NULL),
(2, 'Tops', 1),
(3, 'Jerseys', 2),
(4, 'England', 3);
WITH RECURSIVE
category_tree AS (
SELECT
id,
cat_name,
parent_category_id,
cat_name AS full_name
FROM
categories
WHERE
parent_category_id IS NULL
UNION ALL
SELECT
c.id,
c.cat_name,
c.parent_category_id,
CONCAT (ct.full_name, ' > ', c.cat_name)
FROM
categories c
JOIN category_tree ct ON c.parent_category_id = ct.id
)
SELECT
full_name
FROM
category_tree;
On gists
JavaScript
CSS
CSS trick
Prevent Body Scrolling on iOS
ios-prevent-scroll-fix.js #
const $body = document.querySelector('body');
let scrollPosition = 0;
export default {
enable() {
scrollPosition = window.pageYOffset;
$body.style.overflow = 'hidden';
$body.style.position = 'fixed';
$body.style.top = `-${scrollPosition}px`;
$body.style.width = '100%';
},
disable() {
$body.style.removeProperty('overflow');
$body.style.removeProperty('position');
$body.style.removeProperty('top');
$body.style.removeProperty('width');
window.scrollTo(0, scrollPosition);
}
};
On gists
Nette
PHP router - languages
router.php #
<?php
$sections = [
'cs' => 'clanky',
'sk' => 'blog',
'en' => 'articles',
];
$router->addRoute('[<locale=sk en|cs|sk>/]<section clanky|blog|articles>', [
'presenter' => 'Blog',
'action' => 'default',
null => [
Route::FILTER_IN => function (array $params) use ($sections) {
$params['section'] = $sections[$params['locale'] ?? 'sk'];
return $params;
},
Route::FILTER_OUT => function (array $params) use ($sections) {
$params['section'] = $sections[$params['locale'] ?? 'sk'];
return $params;
},
],
]);
On gists
MySql
MySql tricks
MySql - advanced
MySQL can't specify target table for update in FROM clause
super-workaround.sql #
/*
does not work!
*/
update Users set Valid = 0
where Id in (
select Id from Users where Id < 100
)
/*
workaround
+ one subselect more like a wrapper ;-)
*/
update Users set Valid = 0
where Id in (
select Id from (
select Id from Users where Id < 100
) as t
)
On gists
Vue.js
Expose Slots From a Child Component
ThirdPartyComponent.vue #
<template>
<slot name="header" :header="header"> header {{ header }}</slot>
<slot :default="default"> default {{ default }}</slot>
<slot name="footer" :footer="footer"> footer {{ footer }}</slot>
</template>
<script>
export default {
data() {
return {
header: 'Header value',
default: 'Default value',
footer: 'Footer value',
};
},
};
</script>
On gists
Watchefffect (Oldvalues & NewValues at the same time ;-))
•
Vue.js
watchEffect.js #
// onInvalidate
import { watchEffect, ref } from 'vue'
export default {
setup() {
const count = ref(0)
watchEffect((onInvalidate) => {
const oldValue = count.value // Store the current value as the old value
onInvalidate(() => {
const newValue = count.value // Get the new value when the effect is invalidated
console.log(`Count changed from ${oldValue} to ${newValue}`)
})
})
return { count }
}
}