marsapp / sqlhelper-yii2
Provide sql helper for Yii2
Installs: 2 099
Dependents: 0
Suggesters: 0
Security: 0
Stars: 0
Watchers: 2
Forks: 0
Open Issues: 0
This package is auto-updated.
Last update: 2024-10-23 01:51:17 UTC
README
Provides assistance in using functions to handle SQL grammar construction.
Installation
Composer Install
# composer require marsapp/sqlhelper-yii2
API Reference
batchUpdate
Description
Help construct the query syntax for batch updates by using the functions.
batchUpdate($table, $columns, $rows, $conditionArray, $conditionColumn) : \yii\db\Command
Parameters
- $table: table name
- $columns: Fields to be processed
- $rows: The values to be processed
- $conditionArray: The value of the target field
- $conditionColumn: The name of the target field
Return Values
- Returns \yii\db\Command
Usage
We can use batchUpdate via SqlHelper as follows:
// Create SqlHelper Object $sqlHelper = new \marsapp\helper\sql\SqlHelper(); // Setting batch update data $query = $sqlHelper->batchUpdate('account', ['c_name', 'age'], [ ['Mars', 35], ['Gunter', 24], ['Molly', 25], ],[ 1, 2, 3],'id'); // Run batch update $data = $query->execute(); // Echo last query statement echo $quiery->getRawSql(); echo "\n"; var_export($data);
SQL syntax
The database syntax constructed by batchUpdate is as follows:
UPDATE `account` SET `c_name` = CASE `id` WHEN 1 THEN 'Mars' WHEN 2 THEN 'Gunter' WHEN 3 THEN 'Molly' END, `age` = CASE `id` WHEN 1 THEN 35 WHEN 2 THEN 24 WHEN 3 THEN 25 END WHERE `id` IN (1,2,3);
whereInChunk
Description
Help us split the array by using the functions.
whereInChunk($fieldName, $fieldList, $query, $size) : ActiveQuery
Parameters
- $fieldName: Field to be processed
- $fieldList: The values to be processed
- $query: ActiveQuery
- $size: Cutting length
Return Values
- Returns ActiveQuery
Usage
We can use whereInChunk via SqlHelper as follows:
// Setting Arguments $fieldName = 'pkey'; $fieldList = [1, 2, 3, 4]; $query = ActiveRecord::find(); $size = 3; $data = \marsapp\helpers\sql\SqlHelper::whereInChunk($fieldName, $fieldList, $query, $size) ->asArray() ->all(); // Print Query Syntax echo $query->createCommand()->sql; echo "\n"; echo $query->createCommand()->getRawSql(); echo "\n"; // Print Data var_export($data);
SQL syntax
The database syntax constructed by batchUpdate is as follows:
SELECT * FROM `TABLE_NAME`
WHERE
(`pkey` IN(1, 2, 3)) OR (`pkey` = 4)
timeIntersect
Description
Help construct the query syntax for the intersection of time periods by using the functions.
timeIntersect($sCol, $eCol, $sDate, $eDate, $query) : ActiveQuery
Parameters
- $sCol: Field name for start date
- $eCol: Field namd for end date
- $sDate: Value for start date
- $eDate: Value for end date
- $query: ActiveQuery
Return Values
- Returns ActiveQuery
Usage
We can use timeIntersect via SqlHelper as follows:
// Setting Arguments $sCol = 'start_date'; $eCol = 'endd_date'; $sDate = '2020-04-01'; $eDate = '2020-04-30'; // Sql Builder $query = ActiveRecord::find(); $data = \marsapp\helpers\sql\SqlHelper::timeIntersect($sCol, $eCol, $sDate, $eDate, $query) ->asArray() ->all(); // Print Query Syntax echo $query->createCommand()->sql; echo "\n"; echo $query->createCommand()->getRawSql(); echo "\n"; // Print Data var_export($data);
SQL syntax
The database syntax constructed by batchUpdate is as follows:
SELECT * FROM `TABLE_NAME`
WHERE NOT
(
(`start_date` > '2020-04-30') OR(
(`endd_date` < '2020-04-01') AND(`endd_date` <> '0000-00-00')
)
)