PlItems

ID: 302
PlCodelang: Yii2
PlGroup: Sessions
PlItemTitle: Werte in Session speichern und lesen
Link Reference: https://stackoverflow.com/questions/25079781/how-to-add-more-user-identity-session-attributes-in-yii2
Link Credits: https://stackoverflow.com/a/25080633
Code: \Yii::$app->session->set('user.attribute',$value); \Yii::$app->session->get('user.some_attribute');

ID: 301
PlCodelang: PL/pgSQL
PlGroup: Array
PlItemTitle: Über alle Array-Elemente interieren
Link Reference: https://stackoverflow.com/questions/9783422/loop-over-array-dimension-in-plpgsql
Link Credits: https://stackoverflow.com/a/9784986
Code: DO $do$ DECLARE m text[]; arr text[] := '{{key1,val1},{key2,val2}}'; -- array literal BEGIN FOREACH m SLICE 1 IN ARRAY arr LOOP RAISE NOTICE 'another_func(%,%)', m[1], m[2]; END LOOP; END $do$; -- --------------------------------------------------------- DO $do$ DECLARE i text; arr text[] := '{key1,key2}'; -- array literal BEGIN FOREACH i IN ARRAY arr LOOP RAISE NOTICE 'another_func(%)', i; END LOOP; END $do$;

ID: 300
PlCodelang: BigQuery
PlGroup: Datenbank
PlItemTitle: UPDATE SQL mit JOIN
Link Credits: Yannik :-)
Code: UPDATE `project.dataset.table1` t1 SET t1.target_column = t2.source_column FROM `project.dataset.table2` t2 WHERE t1.join_key = t2.join_key;

ID: 299
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Alle Datenbanken
Code: SELECT "oid", datname, -- database name datdba, "encoding", datcollate, datctype, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl FROM pg_catalog.pg_database WHERE datistemplate = FALSE -- do not list template0, template1, ... ;
Result Example:

oid	datname	datdba	encoding	datcollate	datctype	datistemplate	datallowconn	datconnlimit	datlastsysoid	datfrozenxid	datminmxid	dattablespace	datacl
12345	postgres	10	6	de_DE.UTF-8	de_DE.UTF-8	false	true	-1	13604	478	1	1663
1	template1	10	6	de_DE.UTF-8	de_DE.UTF-8	true	true	-1	13604	478	1	1663	{=c/postgres,postgres=CTc/postgres}

ID: 298
PlCodelang: EXASOL
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Link Technical Documentation: https://docs.exasol.com/db/latest/sql_references/formatmodels.htm
Code: SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DDTHH24:MI:SS.FF3') AS iso_date_string
Result Example:

ISO_DATE_STRING
2021-06-20T17:12:34.567

ID: 297
PlCodelang: BigQuery
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Code: SELECT FORMAT_TIMESTAMP("%FT%H:%M:%E3S", CURRENT_DATETIME()) AS iso_date_string
Result Example:

ISO_DATE_STRING
2021-06-20T17:12:34.567

ID: 296
PlCodelang: EXASOL
PlGroup: Datenmengen
PlItemTitle: Differenzen anzeigen
Code: WITH a AS ( SELECT 1 AS n UNION ALL SELECT 2 AS n UNION ALL SELECT 3 AS n UNION ALL SELECT 4 AS n ), b AS ( SELECT 4 AS n UNION ALL SELECT 5 AS n UNION ALL SELECT 6 AS n UNION ALL SELECT 7 AS n ) SELECT * FROM a EXCEPT -- <------------------------------ SELECT * FROM b
Result Example:

N
1
2
3

ID: 295
PlCodelang: BigQuery
PlGroup: Datenbank
PlItemTitle: Eine Menge B reduziert um Menge A
Code: -- Siehe BigQuery -> Differenzen anzeigen -- https://proglang.meta-grid.com/index.php?r=plItems/view&id=294

ID: 294
PlCodelang: BigQuery
PlGroup: Datenmengen
PlItemTitle: Differenzen anzeigen
Link Reference: https://gist.github.com/yancya/bf38d1b60edf972140492e3efd0955d0
Code: WITH a AS ( SELECT * FROM UNNEST([1,2,3,4]) AS n ), b AS ( SELECT * FROM UNNEST([4,5,6,7]) AS n ) SELECT * FROM a EXCEPT DISTINCT -- <------------------ SELECT * FROM b
Result Example:

n
1
2
3

ID: 293
PlCodelang: BigQuery
PlGroup: Datum, Zeit
PlItemTitle: Gestriges Datum
Code: SELECT CURRENT_TIMESTAMP() AS today_timestamp , DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) AS yesterday_as_date
Result Example:

today_timestamp	yesterday_as_date
2024-02-07 13:40:37.903	2024-02-06

ID: 292
PlCodelang: Yii2
PlGroup: Yii2-Internal
PlItemTitle: Prüfen ob aktueller User ein Gast ist
Code: use Yii; if (! \Yii::$app->user->isGuest) { $result = "User is logged in. User is no guest" }

ID: 291
PlCodelang: SQLite
PlGroup: Strings
PlItemTitle: Führende Nullen vor einer Zahl
Link Reference: https://stackoverflow.com/questions/4899832/sqlite-function-to-format-numbers-with-leading-zeroes
Code: WITH cte_example AS ( SELECT 5 AS int_example UNION ALL SELECT 10 AS int_example UNION ALL SELECT 50 AS int_example ) SELECT int_example, substr('0000000'||ROW_NUMBER() OVER(ORDER BY int_example)||'00', -8, 8) AS example_result FROM cte_example ;
Result Example:

int_example	example_result
5	00000100
10	00000200
50	00000300

ID: 290
PlCodelang: T-SQL
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Code: DECLARE @newDT DATETIME SET @newDT = CAST('2021-01-01 01:23:45' AS DATETIME) SELECT FORMAT(GETDATE(), 'yyyy-MM-dd', 'En') SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss', 'En')
Result Example:

2021-01-01

2021-01-01 01:23:45

ID: 289
PlCodelang: PL/pgSQL
PlGroup: Strings
PlItemTitle: Führende Nullen vor einer Zahl
Code: WITH cte_example AS ( SELECT 5 AS int_example UNION ALL SELECT 10 AS int_example UNION ALL SELECT 50 AS int_example ) SELECT int_example, RPAD(int_example::text, 3, '0'), LPAD(int_example::text, 3, '0') FROM cte_example
Result Example:

---

int_example	rpad	lpad
5	500	005
10	100	010
50	500	050

ID: 288
PlCodelang: PL/pgSQL
PlGroup: Datum, Zeit
PlItemTitle: Gestriges Datum
Code: SELECT (now())::date SELECT (now() + interval '-1 day')::date
Result Example:

2020-12-13 2020-12-12

ID: 287
PlCodelang: MySQL
PlGroup: Strings
PlItemTitle: Anzahl an Zeichen in einem String ermitteln
Link Reference: https://pisceansheart.wordpress.com/2008/04/15/count-occurrence-of-character-in-a-string-using-mysql/
Code: -- Gets only result with 4 "/" in the the field relativePath SELECT id ,albumRoot ,relativePath ,date ,caption ,collection ,icon ,LENGTH(relativePath) - LENGTH(REPLACE(relativePath,'/','')) AS `occurrences` FROM Albums WHERE LENGTH(relativePath) - LENGTH(REPLACE(relativePath,'/',''))=4 ;

ID: 286
PlCodelang: Yii2
PlGroup: Yii2-ActiveQuery
PlItemTitle: Abfrage mit Subselect
Code: <?php /* SQL Example: SELECT * FROM `Cities` WHERE (`postalcode_id` IN (SELECT `id` FROM `postalcodes` WHERE `postalcode`='12345') ) OR (`region_id` IN (SELECT `id` FROM `regions` WHERE `region`='North') ) */ $active_query_postalcodes_For_Subselect = \app\models\Postalcodes::find()->select("id")->where(["postalcodes" => '12345']); // Attention! No "->all()" at the end to create a subselect! $active_query_regions_For_Subselect = \app\models\Regions::find()->select("id")->where(["region" => 'North']); // Attention! No "->all()" at the end to create a subselect! $model = new \app\models\Cities(); $all_Cities_With_IN_and_OR = $model::find() ->where(['in', "postalcode_id", $active_query_postalcodes_For_Subselect]) ->orWhere(['in', "region_id", $active_query_regions_For_Subselect]) ->all(); ?>

ID: 285
PlCodelang: Bash Script
PlGroup: Filehandling
PlItemTitle: Applikations-/Script-Verzeichnis ermitteln
Link Credits: https://stackoverflow.com/questions/59895/how-to-get-the-source-directory-of-a-bash-script-from-within-the-script-itself
Code: #!/bin/sh # The script is located in /tmp/abc/myscript.sh export AppPath=$(dirname $0) echo $0 # /tmp/abc/myscript.sh echo $AppPath # /tmp/abc
Result Example:

/tmp/abc/myscript.sh /tmp/abc

ID: 284
PlCodelang: Yii2
PlGroup: Datenbank
PlItemTitle: Abfrage mit Aggregration
Code: <?php /* // Example as SQL SELECT FieldA ,COUNT(FieldA) as cnt FROM MainTable WHERE FieldA = 5 GROUP BY FieldA */ $MainTableResultQry = MainTable::find() ->select(['FieldA', 'COUNT(FieldA) as cnt']) ->where(['FieldA' => 5]) ->groupBy(['FieldA']) ->createCommand() ->queryAll(); ?>
Result Example:

---

FieldA	cnt
1000	23

| 1500 | 5 |

$MainTableResultQry[0 => ['FieldA' => 1000, 'cnt' => 23], 1 => [['FieldA' => 1500, 'cnt' => 5]]]

ID: 283
PlCodelang: Yii2
PlGroup: Datenbank
PlItemTitle: Verzeichnis erstellen
Code: <?php // Example as SQL: // SELECT * FROM MainTable // WHERE fieldA=5 AND fieldB IN (SELECT id FROM SubTable WHERE fieldC=551) $fieldC = 551; $SubTable_idQry = SubTable::find()->select('id')->where(['fieldC' => $fieldC]); // Static way: $fieldA = 5; // As result: $fieldA = ThirdTable::find() ->select('fieldA_Reference') ->where(['fieldC' => $fieldC]) ->one(); $MainTableResultQry = MainTable::find() ->where(['in', 'fieldB', $SubTable_idQry]) ->andWhere(['fieldA' => $fieldA]) ->all(); ?>