I'm using Yii1 ORM and I struggled for two hours to figure out an issue with findAll. First, let's see a raw query:
select * from lime_group_l10ns where gid in (232, 234);
this yields:
+------+-----+----------------------+--------------+----------+
| id | gid | group_name | description | language |
+------+-----+----------------------+--------------+----------+
| 1340 | 232 | abc Q927 def | ghi Q926 jkl | en |
| 1342 | 234 | Question group three | | en |
+------+-----+----------------------+--------------+----------+
2 rows in set (0.00 sec)
so far so good. Now, I have written this:
$groups = QuestionGroupL10n::model()->findAll("gid in (232, 234)");
foreach ($groups as $group) {
echo $group->gid . "<br>";
}
which is the equivalent of the query I have given and I would expect it to output
232
234
but it only displays
234
so it seems to only take into account the very last element and skips the one before it. I have resolved this issue via
$fields = ['description', 'group_name'];
foreach ($gids as $gid) {
if ($gid != 0) {
$group = QuestionGroupl10n::model()->find("gid=" . $gid);
if ($this->fixText($group, $fields, $names) || $this->fixText($group, $fields, $additionalNames)) {
$group->save();
}
}
}
where I am looping the group ids previously computed and do some changes if needed. This works. But it does an individual request for each group, which does not look good. How can I make Yii1 ORM findAll to take into account all elements involved? Sometimes I crave to write raw queries instead of ORMs and this is exactly such a moment. Can we say that I'm just missing something and there exists a fix maybe related to the usage?
EDIT
Further information has been asked for. Here's the table's create statement:
CREATE TABLE `lime_group_l10ns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gid` int(11) NOT NULL,
`group_name` text COLLATE utf8mb4_unicode_ci NOT NULL,
`description` mediumtext COLLATE utf8mb4_unicode_ci,
`language` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `lime_idx1_group_ls` (`gid`,`language`)
) ENGINE=MyISAM AUTO_INCREMENT=1943 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
and this is the model's source-code:
<?php
/*
* LimeSurvey
* Copyright (C) 2007-2011 The LimeSurvey Project Team / Carsten Schmitz
* All rights reserved.
* License: GNU/GPL License v2 or later, see LICENSE.php
* LimeSurvey is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.php for copyright notices and details.
*
*/
/**
* Class SurveyLanguageSetting
*
* @property string $language QuestionGroup language code. Note: There is a unique key on qid & language columns combined
* @property string $group_name QuestionGroup dieplay text. The actual question.
* @property string $description QuestionGroup help-text for display
*/
class QuestionGroupL10n extends LSActiveRecord
{
/** @inheritdoc */
public function tableName()
{
return '{{group_l10ns}}';
}
/** @inheritdoc */
public function primaryKey()
{
return 'id';
}
/**
* @inheritdoc
* @return self
*/
public static function model($className = __CLASS__)
{
/** @var self $model */
$model = parent::model($className);
return $model;
}
/** @inheritdoc */
public function relations()
{
$alias = $this->getTableAlias();
return array(
'group' => array(self::BELONGS_TO, QuestionGroup::class, '', 'on' => "$alias.gid = group.gid"),
);
}
public function defaultScope()
{
return array('index' => 'language');
}
/** @inheritdoc */
public function rules()
{
return array(
array('group_name,description', 'LSYii_Validators'),
array('language', 'length', 'min' => 2, 'max' => 20), // in array languages ?
array('gid', 'unique', 'criteria' => array(
'condition' => 'language=:language',
'params' => array(':language' => $this->language)
),
'message' => sprintf(gT("Group ID (gid): “%s” already set with language ”%s”."), $this->gid, $this->language),
),
);
}
/** @inheritdoc */
public function attributeLabels()
{
return array(
'language' => gT('Language'),
'group_name' => gT('Group name'),
);
}
}
EDIT2
A further request was related to the raw query. In order to easily figure it out, I induced temporarily an error into the code:
$groups = QuestionGroupL10n::model()->findAll("gid in (232, 234)foobar");
foreach ($groups as $group) {
echo $group->gid . "<br>";
}
exit;
and this is the error message:
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foobar' at line 1. The SQL statement executed was: SELECT * FROM `lime_group_l10ns` `t` WHERE gid in (232, 234)foobar
So the query is
SELECT * FROM `lime_group_l10ns` `t` WHERE gid in (232, 234)
which is, as you can see the raw query I have given at the start of the question. And, as you can see at the start of the question, when executing this raw query, we end up having two records. If I execute the code in the ORM (without the error of course), then I get a single result.
EDIT3
Plurality:
Code:
$groups = QuestionGroupL10n::model()->findAll();
foreach ($groups as $group) {
echo $group->gid . "<br>";
}
exit;
Output:
260
494
260
260
260
260
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
259
Therefore, as tested multiple times, SomeModel::model()->findAll("<yourquery>") is able to find and loop over multiple results. In this particular case it is extremely strange that 232 and 234 is not among the results, which is further sign of inconsistency of findAll or something that I missed.
TLDR:
$model = new QuestionGroupL10n(); $model->resetScope(); ... $model->findAll() ...
This can be hair pulling, but at the end of the day it's something everyone writing PHP actually has already heard about or knows thanks to the array, the swiss-army-knife of data-structures.
The YII Active Record (AR) model's findAll() method returns an array with zero or more entries (one for each model).
By default the array is integer indexed (0, 1, 2, ...), the array is a list (compare array_is_list().)
However, the specific model in the question is with the following default scope:
<?php
/*
* LimeSurvey
* Copyright (C) 2007-2011 The LimeSurvey Project Team / Carsten Schmitz
* All rights reserved.
* License: GNU/GPL License v2 or later, see LICENSE.php
* LimeSurvey is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.php for copyright notices and details.
*
*/
/**
* Class SurveyLanguageSetting
*
* @property string $language QuestionGroup language code. Note: There is a unique key on qid & language columns combined
* @property string $group_name QuestionGroup dieplay text. The actual question.
* @property string $description QuestionGroup help-text for display
*/
class QuestionGroupL10n extends LSActiveRecord
{
/*** ... ********************************/
public function defaultScope()
{
return array('index' => 'language');
}
/*** ... ********************************/
}
The returned array is a parametrization for the "[...] query criteria. This will be used as the parameter to the constructor of CDbCriteria.". (ref. defaultScope())
The reserved-name 'index' summoned the unwanted behaviour, the array as a container didn't had the capacity to return more than one model per language any longer, as the string value of the language column was used as the array key.
This also explains, why the SQL query looked correct, but the result of the AR query not.
The resetScope() method on the model restores the integer indexing of the array.