You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
<?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();
?>
284
Yii2
Datenbank
Abfrage mit Aggregration
<?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();
?>
285
Bash Script
Filehandling
Applikations-/Script-Verzeichnis ermitteln
#!/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
286
Yii2
Yii2-ActiveQuery
Abfrage mit Subselect
<?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();
?>
287
MySQL
Strings
Anzahl an Zeichen in einem String ermitteln
-- 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
;
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
290
T-SQL
Datum, Zeit
Aktuelles Datum/Uhrzeit
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')
291
SQLite
Strings
Führende Nullen vor einer Zahl
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
;
292
Yii2
Yii2-Internal
Prüfen ob aktueller User ein Gast ist
use Yii;
if (! \Yii::$app->user->isGuest)
{
$result = "User is logged in. User is no guest"
}
