yiisoft / db Goto Github PK
View Code? Open in Web Editor NEWYii Database Library
Home Page: https://www.yiiframework.com/
License: BSD 3-Clause "New" or "Revised" License
Yii Database Library
Home Page: https://www.yiiframework.com/
License: BSD 3-Clause "New" or "Revised" License
I need force index in my project, so I've add the method addHintIndex to set the hints indexes associated to a table.
The param is an assoc array of the hintIndex(s) to be used on the query.
The key is the name of the table to be hinted.
The value is the hint settings or an array of hints settings to apply.
Each hint setting is compose by one to three string values defining the hint and one array defining the index(s) to hint.
Here are some examples:
$query = (new \yii\db\Query())->from(['t' => 'user'])->addHintIndex(['user' => [
['force', 'index', ['primary']],
['ignore', 'index', 'order by', ['i1']],
]])->leftJoin('profile as p', 'user.id = profile.user_id')->addHintIndex(['profile' => [
'use', 'index', ['i2']
]]);
// sql -> SELECT * FROM `user` `t` FORCE INDEX (primary) IGNORE INDEX FOR ORDER BY (i1)
LEFT JOIN `profile` `p` ON user.id = profile.user_id USE INDEX (i2)
$users = User::find()->addHintIndex(['user' => [['use', 'index', ['primary']]]])->one();
// sql -> SELECT * FROM {{%user}} USE INDEX (primary)
I have no experience enought with other drivers than mysql, so just write functionality for mysql and sqlite drivers (the former tested the last pending for testing).
Driver overwritting can be done on:
I just want only use yiisoft/db
, after install composer require yiisoft/db:dev-master
, The exception throwed.
can use db component normally.
Fatal error occured.
Q | A |
---|---|
Yii version | 3.0.? |
PHP version | php7.1 |
Operating system | Centos |
BTW: Emmm... What's the time to use yii3.0 components individually ?
Currently ServiceProviderInterface
has 2 functions:
public function __construct(ContainerInterface $container);
public function register(): void;
Interfaces generally should not have constructors.
In this case I propose changing it to this:
public function register(ContainerInterface $container): void;
This has several advantages:
I don't have any need for these advantages, but I think the code is cleaner and more correct this way.
Нет гибкого конструктора для создания запросов с агрегирующими функциями.
Простой и гибкий конструктор в QueryBuilder::buildSelect() для формирования запросов, включая COUNT(DISTINCT ...) и CASE конструкции.
Ничего.
Q | A |
---|---|
Yii version | 2.0.? |
PHP version | |
Operating system |
$query = ArClass::find();
$query->addSelect('extraField');
$item = $query->one();
$item
has all default fields + 1 extraField
, defined in addSelect()
$item
has only extraField
and does not have default fields.
Q | A |
---|---|
Yii version | 2.0.13.1 |
PHP version | 7.0.23 |
Operating system | Ubuntu 16.04.3 x86_64 |
select
property means "all by default" yii\db\Query:58 /**
* @var array the columns being selected. For example, `['id', 'name']`.
* This is used to construct the SELECT clause in a SQL statement. If not set, it means selecting all columns.
* @see select()
*/
public $select;
if ($this->select === null) {
$this->select = $columns;
} else {
$this->select = array_merge($this->select, $columns);
}
Some use cases:
What I want | How do I do | Is goal Reached? |
---|---|---|
Select all default columns | ArClass::find()->all() // no call select() |
yes |
Select only 2 passed columns | ArClass::find()->select(['col1', 'col2'])->all() |
yes |
Select 2 passed columns +1 more | ArClass::find()->select(['col1', 'col2'])->addSelect('col3')->all() |
yes |
Select all default columns + 1 more | ArClass::find()->addSelect('col3')->all() |
no |
Select all default columns + 1 more | ArClass::find()->select('*')->addSelect('col3')->all() |
sometimes |
Select all default columns + 1 more | ArClass::find()->select(ArClass::tableName() . '.*')->addSelect('col3')->all() |
yes |
In my opinion, query must select all default columns until I define columns set manually. Add meand adding element to some set. $select
defined as all by default, but when you call addSelect()
without calling select()
, that means default not exists, add this.
See also #12249
Current version of \yii\dbConnection is missing $charset property.
This is needed in db-pgsql tests, here:
https://github.com/yiisoft/db-pgsql/blob/2508fee50c1c65705015c2546bc980e8277c8720/tests/unit/ActiveRecordTest.php#L129
I have read an issue (#7) that $charset had to be moved in Schema class, but it is not even here.
What is final thought?
The latest updates from several DB such as MySQL, Oracle, PostgreSQL are all supporting JSON data type with basic operations: create, update, index, validate.
Should we implement a support at PHP layer of Yii Framework to support those JSON operations?
I'd like to make all query properties like $where
, $limit
and so on protected.
I have a model (or multiple models) that support soft delete.
I'd like to limit how these are queried by implementing a custom ActiveQuery
class.
Some things I do or would like to do:
->where()
function, for my models I want to enforce only using andWhere
.I expect to have control over my own query class.
Since most properties are public there is no control, anyone could be writing to them directly.
I think the design would be cleaner and more powerful if these properties would be protected.
Since these classes are subclasses of Component
they have support for magic getters which would provide a backwards compatible upgrade path in most cases.
If you use count() method to return the number of rows in your table, you will get the number but as string not integer. You can check that yourself, just var dump the result of your query. Documentation of count() says that it should return integer, and that is what I was hoping for, but got surprised with result. Is this (not)working as intended or I am missing something ?
Docs: http://www.yiiframework.com/doc-2.0/yii-db-query.html#count%28%29-detail
I was doing very simple query $numberOfUsers = User::find()->count();
I use the latest yii2, downloaded today.
Please add a longtext type in the schema builder!
@neoacevedo commented on Dec 28, 2017, 8:45 PM UTC:
There is a support to create views on migrations?
I mean, yii migrate/create create_view_myview
Just like described in the post yiisoft/yii#2886 (comment)
This issue was moved by samdark from yiisoft/yii2#15426.
yii\db\Query::one()
returns false
if there are no results. In a rare break from consistency, yii\db\ActiveQuery::one()
returns null
instead.
I can’t think of any reason why they would need to return different things, so this is probably just an oversight.
Of the two, null
is probably the better option, as it’s more semantic and would allow the result could be used in conjunction with a ??
operator, e.g
$foo = $query->one() ?? 'default';
So Query::one()
is the method that should change.
When I execute batch inserting into tables with unique index, I hope the batchInsert
method of yii\db\Command
Class have a argument to specify whether to execute INSERT IGNORE INTO
or just INSERT INTO
.
it should include the process of adding parenthesis to allow simpler override in sqlite\QueryBuilder.
Currently the complete build() method is overridden just to add two parenthesis.
Q | A |
---|---|
Yii version | 3.0 |
PHP version | 7.1.17 |
Operating system | Centos 7 |
Next yii\db\Exception: SQLSTATE[HY000]: General error: 1096 No tables used
The SQL being executed was: SELECT * in /home/example.com/public_html/vendor/yiisoft/db/src/Schema.php:664
Stack trace:
#0 /home/example.com/public_html/vendor/yiisoft/db/src/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'SELECT *')
#1 /home/example.com/public_html/vendor/yiisoft/db/src/Command.php(1148): yii\db\Command->internalExecute('SELECT *')
#2 /home/example.com/public_html/vendor/yiisoft/db/src/Command.php(413): yii\db\Command->queryInternal('fetch', NULL)
#3 /home/example.com/public_html/vendor/yiisoft/db/src/Query.php(274): yii\db\Command->queryOne()
#4 /home/example.com/public_html/vendor/yiisoft/active-record/src/ActiveQuery.php(294): yii\db\Query->one(NULL)
#5 /home/example.com/public_html/vendor/yiisoft/active-record/src/BaseActiveRecord.php(111): yii\activerecord\ActiveQuery->one()
#6 /home/example.com/public_html/vendor/yiisoft/yii-base-web/src/models/User.php(83): yii\activerecord\BaseActiveRecord::findOne(Array)
#7 /home/example.com/public_html/vendor/yiisoft/yii-base-web/src/forms/LoginForm.php(77): yii\app\models\User::findByUsername('admin')
#8 /home/example.com/public_html/vendor/yiisoft/yii-base-web/src/forms/LoginForm.php(49): yii\app\forms\LoginForm->getUser()
#9 [internal function]: yii\app\forms\LoginForm->validatePassword('password', NULL, Object(yii\validators\InlineValidator))
#10 /home/example.com/public_html/vendor/yiisoft/core/src/validators/InlineValidator.php(72): call_user_func(Array, 'password', NULL, Object(yii\validators\InlineValidator))
#11 /home/example.com/public_html/vendor/yiisoft/core/src/validators/Validator.php(274): yii\validators\InlineValidator->validateAttribute(Object(yii\app\forms\LoginForm), 'password')
#12 /home/example.com/public_html/vendor/yiisoft/core/src/base/Model.php(364): yii\validators\Validator->validateAttributes(Object(yii\app\forms\LoginForm), Array)
#13 /home/example.com/public_html/vendor/yiisoft/yii-base-web/src/forms/LoginForm.php(63): yii\base\Model->validate()
#14 /home/example.com/public_html/vendor/yiisoft/yii-base-web/src/controllers/SiteController.php(78): yii\app\forms\LoginForm->login()
#15 /home/example.com/public_html/vendor/yiisoft/core/src/base/InlineAction.php(57): yii\app\controllers\SiteController->actionLogin()
#16 /home/example.com/public_html/vendor/yiisoft/core/src/base/Controller.php(160): yii\base\InlineAction->runWithParams(Array)
#17 /home/example.com/public_html/vendor/yiisoft/core/src/base/Module.php(542): yii\base\Controller->runAction('login', Array)
#18 /home/example.com/public_html/vendor/yiisoft/yii-web/src/Application.php(94): yii\base\Module->runAction('site/login', Array)
#19 /home/example.com/public_html/vendor/yiisoft/core/src/base/Application.php(525): yii\web\Application->handleRequest(Object(yii\web\Request))
#20 /home/example.com/public_html/public/index.php(14): yii\base\Application->run()
#21 /home/example.com/public_html/public/index.php(15): {closure}()
#22 {main}
Additional Information:
Array
(
[0] => HY000
[1] => 1096
[2] => No tables used
)
2018-09-09 01:08:28 [999.999.999.999][-][e05d5532cdeea664670fa57b4ff9aec5][info][application] $_GET = []
$_POST = [
'_csrf' => 'MuZl0hEYOz8SIZw_V_qcjAPKtCe29HHIo0h-i56owFYHhTqVXl5NVyVV31Y7nfrHcY_7YumBJKTqPgrZ9M32Ow=='
'LoginForm' => [
'username' => 'admin'
'password' => 'admin'
'rememberMe' => '1'
]
'login-button' => ''
]
Try to add several addHavings to the query.
[having] => [
0 => 'and'
1 => [
0 => '<'
1 => 'miles'
2 => 100
]
2 => [
0 => '<='
1 => 'finalPrice * ((100 - finalDiscount) / 100)'
2 => 'test'
]
3 => [
0 => '<='
1 => 'finalPrice * ((100 - finalDiscount) / 100)'
2 => 'tests'
]
]
[having] => [
0 => 'and'
1 => [
0 => 'and'
1 => [
0 => '<'
1 => 'miles'
2 => 100
]
2 => [
0 => '<='
1 => 'finalPrice * ((100 - finalDiscount) / 100)'
2 => 'test'
]
]
2 => [
0 => '<='
1 => 'finalPrice * ((100 - finalDiscount) / 100)'
2 => 'tests'
]
]
Q | A |
---|---|
Yii version | 2.0.12 |
andHaving must be same as andWhere. Instead of :
if ($this->having === null) {
$this->having = $condition;
} else {
$this->having = ['and', $this->having, $condition];
}
$this->addParams($params);
return $this;
It should be like this:
if ($this->having === null) {
$this->having = $condition;
} elseif (is_array($this->having) && isset($this->having[0]) && strcasecmp($this->having[0], 'and') === 0) {
$this->having[] = $condition;
} else {
$this->having = ['and', $this->having, $condition];
}
$this->addParams($params);
return $this;
Hello,
how to use subjects with joining tables? Ex:
I have Query which representing sql:
SELECT `t`.* FROM `ss_profiles_finances` `t` LEFT JOIN `ss_profiles` `profile` ON `t`.`profile_id` = `profile`.`id` WHERE `profile`.`login` = 'user1455'
I need to update some fields in ss_profiles_finances
with params of first query, in sql:
UPDATE `ss_profiles_finances` `t` LEFT JOIN `ss_profiles` `profile` ON `t`.`profile_id` = `profile`.`id` SET `t`.`settings`=IF( `t`.`settings` IS NULL, 31, `t`.`settings` | 1 ) WHERE `profile`.`login` = 'user1455'
But in the current implementation I can do only
// $query was cached and serialized in previous operation (another request)
$rows = $this->getModel()->updateAll(
[
'settings' => $helperAddFlag( $opts[ 2 ], $this->getModel() )
],
$query->where,
$query->params
);
which generating following sql:
UPDATE `ss_profiles_finances` SET `settings`=IF( `settings` IS NULL, 31, `settings` | 1 ) WHERE `profile`.`login` = 'user1455'
Of course it is not work without joning ss_profiles
table.
On downloading the current master branch and installing everything, I get this following error on access of /public/:
[05-Sep-2018 07:41:16 UTC] An Error occurred while handling another error:
yii\di\exceptions\CircularReferenceException: Circular reference to "yii\db\mysql\Schema" detected while building: yii\db\mysql\Schema; dereferencing: in /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/di/src/AbstractContainer.php:121
Stack trace:
#0 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/di/src/Factory.php(48): yii\di\AbstractContainer->build('yii\\db\\mysql\\Sc...', Array)
#1 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/helpers/BaseYii.php(69): yii\di\Factory->create(Array, Array)
#2 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/db/src/Connection.php(841): yii\helpers\BaseYii::createObject(Array)
#3 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/active-record/src/ActiveRecord.php(391): yii\db\Connection->getSchema()
#4 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/active-record/src/ActiveRecord.php(216): yii\activerecord\ActiveRecord::getTableSchema()
#5 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/active-record/src/ActiveRecord.php(195): yii\activerecord\ActiveRecord::filterCondition(Array)
#6 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/active-record/src/BaseActiveRecord.php(111): yii\activerecord\ActiveRecord::findByCondition(Array)
#7 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-base-web/src/models/User.php(64): yii\activerecord\BaseActiveRecord::findOne(Array)
#8 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/User.php(675): yii\app\models\User::findIdentity(1)
#9 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/User.php(195): yii\web\User->renewAuthStatus()
#10 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/User.php(363): yii\web\User->getIdentity()
#11 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Component.php(142): yii\web\User->getIsGuest()
#12 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-base-web/src/views/layouts/main.php(44): yii\base\Component->__get('isGuest')
#13 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/view/src/view/View.php(324): require('/Applications/M...')
#14 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/view/src/view/View.php(246): yii\view\View->renderPhpFile('/Applications/M...', Array)
#15 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Controller.php(397): yii\view\View->renderFile('/Applications/M...', Array, Object(yii\app\controllers\SiteController))
#16 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Controller.php(383): yii\base\Controller->renderContent('<div class="sit...')
#17 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/ErrorAction.php(173): yii\base\Controller->render('error', Array)
#18 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/ErrorAction.php(152): yii\web\ErrorAction->renderHtmlResponse()
#19 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Action.php(99): yii\web\ErrorAction->run()
#20 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Controller.php(160): yii\base\Action->runWithParams(Array)
#21 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Module.php(542): yii\base\Controller->runAction('error', Array)
#22 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/ErrorHandler.php(103): yii\base\Module->runAction('site/error')
#23 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/ErrorHandler.php(123): yii\web\ErrorHandler->renderException(Object(yii\di\exceptions\NotFoundException))
#24 [internal function]: yii\base\ErrorHandler->handleException(Object(yii\di\exceptions\NotFoundException))
#25 {main}
Previous exception:
yii\di\exceptions\NotFoundException: No definition for "yii\db\mysql\Schema" found in /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/di/src/AbstractContainer.php:161
Stack trace:
#0 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/di/src/AbstractContainer.php(134): yii\di\AbstractContainer->buildWithoutDefinition('yii\\db\\mysql\\Sc...', Array)
#1 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/di/src/AbstractContainer.php(155): yii\di\AbstractContainer->build('yii\\db\\mysql\\Sc...', Array)
#2 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/di/src/AbstractContainer.php(134): yii\di\AbstractContainer->buildWithoutDefinition('yii\\db\\mysql\\Sc...', Array)
#3 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/di/src/Factory.php(48): yii\di\AbstractContainer->build('yii\\db\\mysql\\Sc...', Array)
#4 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/helpers/BaseYii.php(69): yii\di\Factory->create(Array, Array)
#5 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/db/src/Connection.php(841): yii\helpers\BaseYii::createObject(Array)
#6 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/active-record/src/ActiveRecord.php(391): yii\db\Connection->getSchema()
#7 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/active-record/src/ActiveRecord.php(216): yii\activerecord\ActiveRecord::getTableSchema()
#8 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/active-record/src/ActiveRecord.php(195): yii\activerecord\ActiveRecord::filterCondition(Array)
#9 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/active-record/src/BaseActiveRecord.php(111): yii\activerecord\ActiveRecord::findByCondition(Array)
#10 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-base-web/src/models/User.php(64): yii\activerecord\BaseActiveRecord::findOne(Array)
#11 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/User.php(675): yii\app\models\User::findIdentity(1)
#12 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/User.php(195): yii\web\User->renewAuthStatus()
#13 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/User.php(363): yii\web\User->getIdentity()
#14 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Component.php(142): yii\web\User->getIsGuest()
#15 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-base-web/src/views/layouts/main.php(44): yii\base\Component->__get('isGuest')
#16 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/view/src/view/View.php(324): require('/Applications/M...')
#17 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/view/src/view/View.php(246): yii\view\View->renderPhpFile('/Applications/M...', Array)
#18 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Controller.php(397): yii\view\View->renderFile('/Applications/M...', Array, Object(yii\app\controllers\SiteController))
#19 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Controller.php(383): yii\base\Controller->renderContent('<div class="sit...')
#20 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-base-web/src/controllers/SiteController.php(63): yii\base\Controller->render('index')
#21 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/InlineAction.php(57): yii\app\controllers\SiteController->actionIndex()
#22 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Controller.php(160): yii\base\InlineAction->runWithParams(Array)
#23 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Module.php(542): yii\base\Controller->runAction('', Array)
#24 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/yii-web/src/Application.php(94): yii\base\Module->runAction('', Array)
#25 /Applications/MAMP/htdocs/hack/y3/vendor/yiisoft/core/src/base/Application.php(525): yii\web\Application->handleRequest(Object(yii\web\Request))
#26 /Applications/MAMP/htdocs/hack/y3/public/index.php(14): yii\base\Application->run()
#27 /Applications/MAMP/htdocs/hack/y3/public/index.php(15): {closure}()
#28 {main}
as a followup to #8644 / #11775:
getting all tables and views should be consistent across schema implementations.
I have:
$query1 = (new \yii\db\Query())
->select("id, category_id AS type, name")
->from('post')
$query2 = (new \yii\db\Query())
->select('id, type, name')
->from('user')
->orderBy('type');
$query1->union($query2);
This code generates the following SQL:
(SELECT `user`.`id`, `user`.`category_id` AS `type`, `user`.`name` FROM `post`)
UNION
(SELECT `user`.`id`, `user`.`category_id`, `user`.`name` FROM `post` ORDER BY `type`)
The SQL above does not order correctly.
It's necessary remove the parentheses. Ex:
SELECT `user`.`id`, `user`.`category_id` AS `type`, `user`.`name` FROM `post`
UNION
SELECT `user`.`id`, `user`.`category_id`, `user`.`name` FROM `post` ORDER BY `type`
Try to import db schema (from mysqldump or PhpMyAdmin schema export) with some error on non-first query. In example, change "int" column type in some table to "intFOO" to cause mysql error.
Schema:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `auth_assignment`;
CREATE TABLE `auth_assignment` (
`item_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`user_id` intFOO(10) UNSIGNED NOT NULL,
`created_at` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Example code (console action):
public function actionReset()
{
$schemaFile = 'some/path/schema.sql';
try {
$command = Yii::$app->db->createCommand(file_get_contents($schemaFile));
$command->execute();
Console::output('Schema imported.');
return self::EXIT_CODE_NORMAL;
} catch (\Exception $e) {
Console::error($e->getMessage());
}
return self::EXIT_CODE_ERROR;
}
Execution ends with proper exception.
"Schema imported." on stdout - no exception thrown, or error reported.
Read this question and approved answer: How to check if db execute sql fails?
I believe that Yii should handle this case?
Q | A |
---|---|
Yii version | 2.0.12 |
PHP version | 7.1.x |
Operating system | Kubuntu 17.04 |
Its just an idea to make yii\db\QueryBuilder more SRP than it is now.
What if make separate classes for SelectBuilder, InsertBuilder, UpdateBuilder e.t.c
They can be much better to use and understand than the monster yii\db\QueryBuilder
Please say what you think about it
Very often in practice I have to do a bulk update of rows.
IN PostgreSQL:
UPDATE table AS t
SET
col1 = c.col1,
col2 = c.col2
FROM (VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4) ) AS c (id, col1, col2)
WHERE c.id = t.id;
or
INSERT INTO table (id, col1, col2)
VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
ON CONFLICT (id) DO UPDATE SET
col1 = EXCLUDED.col1,
col2 = EXCLUDED.col2
IN MySQL:
INSERT INTO table (id, col1, col2)
VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
ON DUPLICATE KEY UPDATE
col1 = VALUES(col1),
col2 = VALUES(col2);
I'm sure other databases have similar capabilities.
For each DBMS will have its own specific implementation of the method batchUpdate in descendant classes.
Sorry for my bad english.
Duplicate issue on yiisoft/yii2#15426
I have to use the each method to select in batches. The problem is, that I have to delete some columns in this foreach, which generates a table lock. Now I have to set the cursor PDO::ATTR_CURSOR
to forward only PDO::CURSOR_FWDONLY
. Is this even possible by now?
$simpleQuery = SimpleObject::find();
// need something like: $simpleQuery->setPdoStmtAttr(\PDO::ATTR_CURSOR, \PDO::CURSOR_FWDONLY);
foreach ($simpleQuery->each() as $simpleObject) {
// fancy things...
}
I've found many times this souces:
if (!empty($config)) {
\yii\di\AbstractContainer::configure($this, $config);
}
For example, here:
Line 1308 in 54e7a7e
Should not be better that $config empty condition check was done inside AbstractContainer::configure, so we should have only:
\yii\di\AbstractContainer::configure($this, $config);
In QueryInterface
it seems some functions are missing:
public function each()
public function batch()
In Query
, these functions return BatchQueryResult
. Since from an API perspective BatchQueryResult
is equal to Iterator
, I propose changing the return type to iterable
.
By doing this any QueryInterface
implementation can simply implement these functions (we could even implement a basic trait) in an inefficient way:
public function each() {
return $this->all(); // An array is already iterable.
}
// Taken from: https://github.com/nikic/iter/blob/master/src/iter.php#L802
public function batch($100) {
$chunk = [];
$count = 0;
foreach ($this->each() as $key => $value) {
$chunk[$key] = $value;
$count++;
if ($count === $size) {
yield $chunk;
$count = 0;
$chunk = [];
}
}
if ($count !== 0) {
yield $chunk;
}
}
When creating objects that consume a QueryInterface
, what matters not is implementation, but intended use. When I consume an implementation I want to let it know that I'll be iterating over the objects one by one (each
), it should, if possible, do something smart with that information.
Currently I either type hint to a specific implementation or I call functions not on the interface, both are not satisfactory.
If I use Mongo for example, it has an implementation for each
: https://github.com/yiisoft/yii2-mongodb/blob/master/Query.php#L302
However I cannot use this without explicitly adapting my code.
Query
implemention in the framework the precondition will fail (Mongo extension Query
does not extend yii\db\Query
).QueryInterface
I cannot call each()
without making assumptions that might not be valid.This will break backwards compatibility for third party QueryInterface
implementations, these will need to implement this functionality manually or use the trait containing the frameworks' implementations.
If we really want this to be useful independently of yii-core
we should remove the application from the test suite.
We can't guarantee that it works properly without the core if we still initialize our singletons like Yii::$container
and Yii::$app
during testing.
Idea
public function _fixtures(){
return [
'user' => [
'class' => UserFixture::className(),
'dataFile' => codecept_data_dir() . 'user.php'
],
'user1' => [
'class' => (new class extends \yii\test\ActiveFixture
{
public $modelClass = 'dektrium\user\models\User';
}),
'dataFile' => codecept_data_dir() . 'user.php'
],
'user2' => function(){
return Yii::createObject(\yii\test\ActiveFixture::className(),[
'modelClass' => 'dektrium\user\models\User',
'dataFile' => codecept_data_dir() . 'user.php',
]);
},
'token' => [
'class' => TokenFixture::className(),
'dataFile' => codecept_data_dir() . 'token.php'
],
];
}
expand createFixture
like a createObject
Q | A |
---|---|
Yii version | 2.0.? |
PHP version | 7 |
Operating system | Debian |
@licanulla commented on Tue Feb 19 2019
Just found that since upgrade 2.0.16 my select2 fields with ajax option are no longer validated. Those with preset data still are. No JS errors in console. Downgraded to 2.0.15.1 and got my forms working again.
@rob006 commented on Tue Feb 19 2019
Probably duplicate of yiisoft/yii2#17111
How do you generate AJAX response for select2 widget?
@licanulla commented on Tue Feb 19 2019
No AJAX calls in my case. The client-side validation is broken somehow so when the form is loaded and you hit "submit" button, those select2 fields (with ajax
option) left empty aren't marked as required, while they are and the form is initialized with those fields as required.
@rob006 commented on Wed Feb 20 2019
You need to give some code to reproduce this issue.
Hello.
Sometimes I need to insert or update rows. I can do it using 'ON DUPLICATE KEY UPDATE'.
what do you think?
@i-panov commented on Oct 23, 2018, 2:38 PM UTC:
Create new migration and use function $this->createIndex()
.
I want the fourth parameter in this function not the $unique
flag, but the type of the index, for example fulltext
. Because there is no other possibility to create an index (except through the execute
method).
Q | A |
---|---|
Yii version | 2.0.14 |
PHP version | 7.2 |
Operating system | CentOS 7 |
This issue was moved by samdark from yiisoft/yii2#16815.
A database can contain comments on the columns and the table itself. The comment of the columns can be read out trough the ColumnSchema
class, but the comment on the table itself isn't.
Is this something that is open for inclusion in Yii 2? This is a bit related to #6093, but that PR wasn't accepted, so I want to avoid to work on this if it won't be accepted.
This could be used to generate usefull phpdoc with Gii based on the database comments.
Currently both master and slave connections use the same class.
This violates the SRP.
We should have a class that only implements the slave connection part and one class that only implements the master connection.
Feature request for discussion.
When the DB server uses multi-master replication, conflict can occur when clients of different replication masters write to the same row.
In the case of Galera replication (Dealing with Multi-Master Conflicts):
When two transactions come into conflict, the later of the two is rolled back by the cluster. The client application registers this rollback as a deadlock error. Ideally, the client application should retry the deadlocked transaction, but not all client applications have this logic built in.
If this is implemented it should be above the PDO layer but below the APIs that the application uses to access the DB. Hence I wonder if Yii might usefully provide this feature. I guess yii\db\Command::queryInternal()
is roughly where this might happen.
If this were implemented it would
I imagine it would be fairly easy to implement but I it might be quite hard to properly test.
Here $float_val = 1.23, instead db column type is defined as double(4,3), so its value is 1.230.
Line 207 in 1351c16
When checked equality, test fails.
@SamMousa commented on Jul 5, 2018, 3:34 PM UTC:
MariaDB is diverging more and more from MySQL.
Try to use JSON columns with Yii and MariaDB.
It should work.
It doesn't since MySQL and MariaDB have solved the problem in different ways.
mariadb\Schema
and related classes that extend from the MySQL version.JSON
column we must manually add the JSON
validation constraint, see this.Since in MariaDB creating a JSON
column results in a LONGTEXT
column we need to somehow identify which LONGTEXT
columns contain JSON
.
JSON
columns in MariaDB
via column comments or name pattern matching.JSON
column by parsing constraints via SHOW CREATE TABLE
.Since the DSN remains exactly the same. No one will ever use the MariaDB schema / classes, so any additions to Yii are backwards compatible.
For now I propose not doing any detection and just forcing users to set $driverName = 'mariadb';
if they want to use the new classes.
I'm willing to implement this in 2.X, alternatively I'll implement this privately and will merge it into 3.0 when that gets released.
Any feedback is appreciated.
This issue was moved by samdark from yiisoft/yii2#16485.
I have a problem withh overriding class. I know my code is not pretty good, but it have sence anyeay.
Q | A |
---|---|
Yii version | 2.0.16 |
PHP version | 7.0 |
Operating system | Debian |
yii2-query.patch.txt |
The methods execute() and queryInternal() have logging queries by Yii::info($rawSql, _METHOD);
Why 'info' ?
I think it must be 'trace' level.
Yii::trace($rawSql, _METHOD);
Q | A |
---|---|
Yii version | 2.0.8 |
PHP version | 5.6 |
Operating system | ArchLinux |
Also this events should be upgraded to new Event classes.
Line 158 in 7aef171
Is it \yii\db\ConnectionEvent class name right for this purpose ?
@SamMousa commented on Jul 18, 2018, 10:23 AM UTC:
Currently the yii\db\Connection
class has DBMS specific code for setting the charset.
Instead this should be moved to the respective Schema
classes.
protected function initConnection()
{
...
if ($this->charset !== null && in_array($this->getDriverName(), ['pgsql', 'mysql', 'mysqli', 'cubrid'], true)) {
$this->pdo->exec('SET NAMES ' . $this->pdo->quote($this->charset));
}
$this->trigger(self::EVENT_AFTER_OPEN);
}
A better solution would be to have the Schema
initiate the character set, or more generic the connection. This could be done by simply initiating it in initConnection()
:
protected function initConnection()
{
$this->getSchema();
$this->trigger(self::EVENT_AFTER_OPEN);
}
This issue was moved by samdark from yiisoft/yii2#16530.
Object Form in yii\db\Query
conditions introduced in Yii 2.0.14 is really great and timely enhancement!
It would be nice although setting its escapingReplacements
property in a more convenient way, than calling its setter method:
$likeObject->setEscapingReplacements( false );
The property itself is not public
but protected
, so direct assignment raises exception:
$likeObject->escapingReplacements = false;
In general, it would be nice to set escapingReplacements
at object creation as fouth parameter passed to __construct
method, e.g. like this:
$data = ( new Query())
->select([ 'id', 'name' ])
->from( 'locality' )
->where( new LikeCondition( 'postal', 'LIKE', '12345_', false ))
->all();
This would eliminate the need in additional object holding var and code lines:
$likeObject = new LikeCondition( 'postal', 'LIKE', '12345_' );
$likeObject->setEscapingReplacements( false );
$data = ( new Query())
->select([ 'id', 'name' ])
->from( 'locality' )
->where( $likeObject )
->all();
Q | A |
---|---|
Yii version | 2.0.14 |
PHP version | 5.6.12 |
Operating system | Windows |
$db = db()->createCommand("
UPDATE bet
SET status = subquery.winr
FROM (:subquery) AS subquery
WHERE bet.id = subquery.id
", ['subquery' => new Expression($this->leadersQuery()->select(['id', 'winr'])->createCommand()->rawSql)])->execute();
//->update(/*the truth is out there*/); needed
Use MySQL database with sql_mode set to ANSI_QUOTES
QueryBuilder::addCommentOnColumn
works as expected
QueryBuilder::addCommentOnColumn
crashes with a MySQL syntax error, because QueryBuilder::getColumnDefinition
returns null
, because its regular expression pattern suddenly does not work.
I am already preparing a fix (a pull request)
New feature, adding the possibility to use CASE WHEN in yii\db\Query
@teo1978 commented on Jul 29, 2018, 3:42 PM UTC:
MySQL's "utf8
" charset, which shouldn't even be called that since it is not actual utf8, was a disgrace that happened years ago because someone in the MySQL team was stupid. The real utf8-conforming character set in MySQL is utf8mb4
. In 2018, we should be using that and encouraging to use that as a default for MySQL.
Any hypothetical compatibility issue with old MySQL versions, I don't think is any more stringent, and worth caring about, than current existing incompatibilities with obsolete php versions, for example.
And the disastrous effects of using MySQL's "utf8
" charset are much worse.
utf8mb4
utf8mb4_unicode_ci
as the default collation. Then I configured main_local.php
with 'dsn' => 'mysql:host=localhost;dbname=iframe2',
//...
'charset' => 'utf8mb4',
I followed the rest of instructions (including running the migration) and have the application up and running, and I notice that the user
table that has been created has utf8_unicode_ci
(i.e. "utf8
" charset) as its default and for all its fields.
I'm not sure whether this is a specific choice for the User model table based on the belief that allowing 4-byte characters such as smilies in a username wouldn't be a good idea (which could be questionable but arguable) or if this comes from a general "we use utf8
everywhere by default" policy. If it's the latter, then I'm seriously worried.
I wouldn't like to see any more tables created by the framework with utf8
specifically forced as the charset despite the database default being utf8mb4
. I can understand forcing a specific charset rather than using the database default for some things, but then that specific charset should be utf8mb4
(which again is true utf-8) for anything that doesn't have a specific reason to be something else.
In the Getting Started guides both for the basic and advanced apps I see you suggest utf8
as the charset in the configuration for a MySQL database. Again, it should be utf8mb4
the actual config file provided with the basic app also has utf8
.
Hopefully it's just a matter of the default configuration files provided with the template application projects and the examples in the docs (and apparently something else in the Advanced app, because utf8
was assigned to the user model table despite the database default and the config), and not much more than that.
This issue was moved by samdark from yiisoft/yii2#16576.
we should add one function copyTable($table,$newName,$onlyStructure = true)
I hope it could provide a function to copy table efficient
There is more at play here than the "order of elements" argument:
Regardless of the argument that you can construct all kinds of arrays in PHP I propose documenting a preferred structure and actually checking if the code conforms...
Create code like this:
$x->batchInsert('table1', ['a', 'b'], ['b' => 123, 'a' => 345]);
I expect it to work properly, ie create a query like this:
insert into `table1` (`a`, `b`) values (345, 123);
insert into `table1` (`a`, `b`) values (123, 345);
The thing that is wrong here is not so much the code as my assumption: batch insert does not support associative arrays; this means things like column order and also column type casting will go wrong when passing in associative arrays.
My proposal is to actively check each key in the row data and throw a hard exception if it's not numerical. Since we're already iterating we can do this without a significant performance cost; the only lines we'd add are something like this:
if (!is_int($i)) {
throw new SomeKindOfException('Data rows must be numerically indexed');
}
Q | A |
---|---|
Yii version | latest |
PHP version | N/A |
Operating system | N/A |
Related issues | #14608 |
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.