Hi All,
I am new to Yii2 framework. Just month into this framework.
Currently I am working on Yii2 REST API.
Detail description of problem:
Actual Problem:
I want to filter data of country table with relations to state. Right now I am not working with relations (hence commented out in code).
Its simple datafilter implementation as per code.
It works for
But when I work with >= condition or any condition with ‘=’ in filter, breaks and gives error.
Error Response
<name>Database Exception</name>
<message>SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type integer: "]1" The SQL being executed was: SELECT COUNT(*) FROM "country" WHERE ("id"=']1') AND ("id"=']1')</message>
<code>22P02</code>
<type>yii\db\Exception</type>
<file>/var/www/html/tasktwo/vendor/yiisoft/yii2/db/Schema.php</file>
<line>678</line>
Setup:
Framework version : 2.0.42.1
Database: PostgreSQL [PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit]
PHP and Apache : Apache/2.4.41 (Ubuntu)
PHP 7.4.18.
Code:
Migration file for country:
<?php
use yii\db\Migration;
/**
* Handles the creation of table `{{%country}}`.
*/
class m210616_054336_create_country_table extends Migration
{
/**
* {@inheritdoc}
*/
public function safeUp()
{
$this->createTable('{{%country}}', [
'id' => $this->primaryKey(),
'countryname' => $this->string(100),
'countrycode' => $this->string(10),
'file_url' => $this->string(10),
'created_at' => $this->integer(),
'updated_at' => $this->integer(),
'created_by' => $this->integer(),
'updated_by' => $this->integer(),
'is_active' => $this->smallInteger(1)
]);
$this->addForeignKey('fk_country_user_created_by',
'{{%country}}',
'created_by', '{{%user}}', 'id');
$this->addForeignKey('fk_country_user_updated_by',
'{{%country}}',
'updated_by', '{{%user}}', 'id');
}
/**
* {@inheritdoc}
*/
public function safeDown()
{
$this->dropForeignKey('fk_country_user_created_by', '{{%country}}');
$this->dropForeignKey('fk_country_user_updated_by', '{{%country}}');
$this->dropTable('{{%country}}');
}
}
Country.php (Model)
<?php
namespace api\models;
use Yii;
/**
* This is the model class for table "{{%country}}".
*
* @property int $id
* @property string|null $countryname
* @property string|null $countrycode
* @property string|null $file_url
* @property int|null $created_at
* @property int|null $updated_at
* @property int|null $created_by
* @property int|null $updated_by
* @property int|null $is_active
*
* @property User $createdBy
* @property User $updatedBy
* @property State[] $states
*/
class Country extends \yii\db\ActiveRecord
{
/**
* {@inheritdoc}
*/
public static function tableName()
{
return '{{%country}}';
}
/**
* {@inheritdoc}
*/
public function rules()
{
return [
[['created_at', 'updated_at', 'created_by', 'updated_by', 'is_active'], 'default', 'value' => null],
[['created_at', 'updated_at', 'created_by', 'updated_by', 'is_active','id'], 'integer'],
[['countryname'], 'string', 'max' => 100],
[['countrycode', 'file_url'], 'string', 'max' => 10],
[['created_by'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['created_by' => 'id']],
[['updated_by'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['updated_by' => 'id']],
];
}
/**
* {@inheritdoc}
*/
public function attributeLabels()
{
return [
'id' => 'ID',
'countryname' => 'Countryname',
'countrycode' => 'Countrycode',
'file_url' => 'File Url',
'created_at' => 'Created At',
'updated_at' => 'Updated At',
'created_by' => 'Created By',
'updated_by' => 'Updated By',
'is_active' => 'Is Active',
];
}
/**
* Gets query for [[CreatedBy]].
*
* @return \yii\db\ActiveQuery|\api\models\query\UserQuery
*/
public function getCreatedBy()
{
return $this->hasOne(User::className(), ['id' => 'created_by']);
}
/**
* Gets query for [[UpdatedBy]].
*
* @return \yii\db\ActiveQuery|\api\models\query\UserQuery
*/
public function getUpdatedBy()
{
return $this->hasOne(User::className(), ['id' => 'updated_by']);
}
/**
* Gets query for [[States]].
*
* @return \yii\db\ActiveQuery|\api\models\query\StateQuery
*/
public function getStates()
{
return $this->hasMany(State::className(), ['parent_id' => 'id']);
}
/**
* Gets query for [[GrandParent]].
*
* @return \yii\db\ActiveQuery|\api\models\query\CountryQuery
*/
public function getCities()
{
return $this->hasMany(City::className(), ['parent_id' => 'id'])->via('states');
}
/**
* {@inheritdoc}
* @return \api\models\query\CountryQuery the active query used by this AR class.
*/
public static function find()
{
return new \api\models\query\CountryQuery(get_called_class());
}
//displaying only few needed fields
public function fields()
{
return[
'countryid'=>'id',
'countryname',
'countrycode',
];
}
public function extraFields()
{
return[
'state' => 'states',
'city' => 'cities',
];
}
}
CountrySearch.php
<?php
namespace api\models\search;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use api\models\Country;
use api\models\City;
use yii\data\ActiveDataFilter;
/**
* CountrySearch represents the model behind the search form of `api\models\Country`.
*/
class CountrySearch extends Country
{
public $cityid,$cityname,$stateid,$statename;
/**
* {@inheritdoc}
*/
public function rules()
{
return [
[['created_at', 'updated_at', 'created_by', 'updated_by', 'is_active'], 'integer'],
[['states','cities','countryname', 'countrycode', 'file_url','id','statename','cityname'], 'safe'],
];
}
/**
* {@inheritdoc}
*/
public function scenarios()
{
// bypass scenarios() implementation in the parent class
return Model::scenarios();
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Country::find();
// ->joinWith('states','cities');
$filter = new ActiveDataFilter([
'searchModel' => $this,
]);
$filterCondition = null;
// You may load filters from any source. For example,
// if you prefer JSON in request body,
if ($filter->load($params)) {
$filterCondition = $filter->build();
if ($filterCondition === false) {
// Serializer would get errors out of it
return $filter;
}
}
if ($filterCondition !== null) {
$query->andWhere($filterCondition);
}
$dataProvider= new ActiveDataProvider([
'query' => $query,
]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
return $dataProvider;
}
}
CountryController.php
<?php
namespace api\controllers;
use Yii;
use api\models\Country;
use api\models\search\CountrySearch;
use yii\filters\Cors;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;
use yii\rest\ActiveController;
/**
* CountryController implements the CRUD actions for Country model.
*/
class CountryController extends ActiveController
{
public $modelClass='api\models\Country';
/**
* {@inheritdoc}
*/
public function behaviors()
{
$behaviors=parent::behaviors();
$behaviors['cors']=Cors::class;
return $behaviors;
}
public function actions(){
$searchModel=new CountrySearch();
$actions = parent::actions();
$actions['index']['prepareDataProvider'] = [$this, 'prepareDataProvider'];
$actions['index']['dataFilter'] = [
'class' => \yii\data\ActiveDataFilter::class,
'searchModel' => $searchModel,
];
unset($actions['delete']);
return $actions;
}
/**
* Lists all Country models.
* @return mixed
*/
public function prepareDataProvider()
{
$searchModel = new CountrySearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);
return $dataProvider;
}
/**
* Finds the Country model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $id
* @return Country the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = Country::findOne($id)) !== null) {
return $model;
}
throw new NotFoundHttpException('The requested page does not exist.');
}
}
type or paste code here
Any thing missing from my side?
2nd:
What is best way to solve ambiguous column issue when dealing with relations and filter?
Thank you in adavance.