Apply dataSource filtering on related tables #3
Labels
Comments
|
I plan to implement the filters for extra fields but it is not so easy. |
|
Hi Tigrov
I managed to make it work with table alias in the query, but I had to do
the following:
Consider I have a model Product and its related the model ProductBrand, the
tables are related with a fk like so *product.product_brand_id =
product_brand.id <http://product_brand.id>*
So in the query in the kendoActions() I make a query like
* $options = [ 'model' =>
Product::className(), 'query' => [ 'where' =>
[ 'join' => [['INNER JOIN', 'product_brand AS
productBrand','product.product_brand_id = productBrand.id'
]] ], ], 'extendMode' =>
true, 'extraFields' => [ 'productBrand' ] ];
return KendoBuild::actions($options);*
In my Product model I have ofcourse the extraFields like
* public function extraFields() { return [
'productBrand', ]; }*
....
* public function getProductBrand() { return
$this->hasOne(ProductBrand::className(), ['id' => 'product_brand_id']);
}*
And final step is to define de kendo column like
* { title: "Brand",*
* field: "productBrand.Name", }*
But this only works if I comment out your code in ParamConverter.php file,
in the part where you get the columns from the $model->getTableSchema()->
columns;
if (!empty($filter['field'])
&& isset($filter['value']) && !empty($filter['operator'])
) {
$db = $model->getDb();
$tableName = $model::tableName();
//$attribute = $tableName . '.' . $filter['field'];
$attribute = $filter['field'];
$value = $operator = null;
//$type = $columns[$filter['field']]->type;
$numberOperators = static::NUMBER_OPERATORS;
if (isset($numberOperators[$filter['operator']])) {
$operator = $numberOperators[$filter['operator']];
//$value = static::parseDate($filter['value']);
//if ($value) {
// if (in_array($type, [Schema::TYPE_INTEGER,
Schema::TYPE_BIGINT])) {
// $fromUnixtime = $db->driverName == 'pgsql' ?
'TO_TIMESTAMP' : 'FROM_UNIXTIME';
// $attribute = 'DATE(' . $fromUnixtime . '(' .
$db->quoteColumnName($attribute) . '))';
// } elseif (in_array($type, [Schema::TYPE_TIMESTAMP,
Schema::TYPE_DATE, Schema::TYPE_DATETIME, Schema::TYPE_TIME])) {
// $attribute = 'DATE(' . $db->quoteColumnName($attribute) . ')';
// } else {
// $value = null;
// }
//}
if ($value === null) {
if (is_numeric($filter['value'])) {
$value = (float)$filter['value'];
} elseif (in_array($filter['value'], ['true', 'false'])) {
$operator = $filter['value'] == 'false'
? $numberOperators['eq']
: $numberOperators['neq'];
$value = 0;
}
}
}
$stringOperators = static::STRING_OPERATORS;
if ($value === null && isset($stringOperators[$filter['operator']])) {
$operator = $stringOperators[$filter['operator']];
$value = $filter['value'];
if ($filter['operator'] == 'contains' || $filter['operator']
== 'doesnotcontain') {
$value = "%$value%";
} elseif ($filter['operator'] == 'startswith') {
$value = "$value%";
} elseif ($filter['operator'] == 'endswith') {
$value = "%$value";
}
return $value !== null ? [$operator, $attribute, $value, false] : null;
}
return $value !== null ? [$operator, $attribute, $value] : null;
Of course it doesnt work with date columns.
And this code was before your last commit
Thank you,
…On Mon, Jan 9, 2017 at 4:40 PM, Tigrov ***@***.***> wrote:
I plan to implement the filters for extra fields but it is not so easy.
And I can't tell you when it will be done.
You are welcome to pull requests.
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
<#3 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/AIlYEwyOTBjI3RBcSd2AE9CjZAU8gSnQks5rQrdvgaJpZM4LbFCq>
.
|
|
Tigrov,
I was thinking that maybe if you get the attributes from the query in
instead of the columns of the table when extendMode is used? Maybe its the
way to go.
Thank you,
…On Tue, Jan 10, 2017 at 9:13 AM, Jose A Leon ***@***.***> wrote:
Hi Tigrov
I managed to make it work with table alias in the query, but I had to do
the following:
Consider I have a model Product and its related the model ProductBrand,
the tables are related with a fk like so *product.product_brand_id =
product_brand.id <http://product_brand.id>*
So in the query in the kendoActions() I make a query like
* $options = [ 'model' =>
Product::className(), 'query' => [ 'where' =>
[ 'join' => [['INNER JOIN', 'product_brand AS
productBrand','product.product_brand_id = productBrand.id'
]] ], ], 'extendMode' =>
true, 'extraFields' => [ 'productBrand' ] ];
return KendoBuild::actions($options);*
In my Product model I have ofcourse the extraFields like
* public function extraFields() { return [
'productBrand', ]; }*
....
* public function getProductBrand() { return
$this->hasOne(ProductBrand::className(), ['id' => 'product_brand_id']);
}*
And final step is to define de kendo column like
* { title: "Brand",*
* field: "productBrand.Name", }*
But this only works if I comment out your code in ParamConverter.php file,
in the part where you get the columns from the
$model->getTableSchema()->columns;
if (!empty($filter['field'])
&& isset($filter['value']) && !empty($filter['operator'])
) {
$db = $model->getDb();
$tableName = $model::tableName();
//$attribute = $tableName . '.' . $filter['field'];
$attribute = $filter['field'];
$value = $operator = null;
//$type = $columns[$filter['field']]->type;
$numberOperators = static::NUMBER_OPERATORS;
if (isset($numberOperators[$filter['operator']])) {
$operator = $numberOperators[$filter['operator']];
//$value = static::parseDate($filter['value']);
//if ($value) {
// if (in_array($type, [Schema::TYPE_INTEGER, Schema::TYPE_BIGINT])) {
// $fromUnixtime = $db->driverName == 'pgsql' ? 'TO_TIMESTAMP' : 'FROM_UNIXTIME';
// $attribute = 'DATE(' . $fromUnixtime . '(' . $db->quoteColumnName($attribute) . '))';
// } elseif (in_array($type, [Schema::TYPE_TIMESTAMP, Schema::TYPE_DATE, Schema::TYPE_DATETIME, Schema::TYPE_TIME])) {
// $attribute = 'DATE(' . $db->quoteColumnName($attribute) . ')';
// } else {
// $value = null;
// }
//}
if ($value === null) {
if (is_numeric($filter['value'])) {
$value = (float)$filter['value'];
} elseif (in_array($filter['value'], ['true', 'false'])) {
$operator = $filter['value'] == 'false'
? $numberOperators['eq']
: $numberOperators['neq'];
$value = 0;
}
}
}
$stringOperators = static::STRING_OPERATORS;
if ($value === null && isset($stringOperators[$filter['operator']])) {
$operator = $stringOperators[$filter['operator']];
$value = $filter['value'];
if ($filter['operator'] == 'contains' || $filter['operator'] == 'doesnotcontain') {
$value = "%$value%";
} elseif ($filter['operator'] == 'startswith') {
$value = "$value%";
} elseif ($filter['operator'] == 'endswith') {
$value = "%$value";
}
return $value !== null ? [$operator, $attribute, $value, false] : null;
}
return $value !== null ? [$operator, $attribute, $value] : null;
Of course it doesnt work with date columns.
And this code was before your last commit
Thank you,
On Mon, Jan 9, 2017 at 4:40 PM, Tigrov ***@***.***> wrote:
> I plan to implement the filters for extra fields but it is not so easy.
> And I can't tell you when it will be done.
> You are welcome to pull requests.
>
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, view it on GitHub
> <#3 (comment)>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AIlYEwyOTBjI3RBcSd2AE9CjZAU8gSnQks5rQrdvgaJpZM4LbFCq>
> .
>
|
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Is there a way or a chance that you can implement the filters on the dataSource for the joined tables? or the extraFields of the model
for example in the ActiveRecord Model extraFields
I have tried to use the filters on fields or extraFields and cannot make it work
It only filters on columns of the ActiveRecord class
The text was updated successfully, but these errors were encountered: