Suggested behavior for Query

[tonydspaniard]

Currently in order to make a search function (not following gii approach) a user needs to do the following:

$query = Contact::find();
if(!empty($this->full_name))
{
   $query->where(['like', 'full_name', $this->full_name]);
}
// or 
if(trim($this->email) !== '')
{
   $query->where(['email' => $this->email]);
}

Or using the gii approach (creating an addCondition method) it is always required to search for empty or NULL values. Which even I think is good for demo purposes to have the addCondition on the search model reproduced by gii, I find it quite of non-sense to have the same method on each search model.

Failing to not check against NULL values will cause two different results on both scenarios. On the first one like, it throws an error:

Whereas on the second one will produce the following SQL:

SELECT * FROM tbl_contact WHERE full_name IS NULL LIMIT 10

Which IMHO I think is the wrong behavior, even if the adCondition function of the search model strictly checks for empty strings not NULL attributes.

_Suggestion_

I believe that everybody should be able to use the following:

$query = Contact::find();
$query->where(['like', 'full_name', $this->full_name]);
$query->where(['email' => $this->email]);

And NULL or empty values should be removed from the resulting Query object as it used to happen with CDbCriteria on Yii1. That will make our search classes cleaner and without the need to check whether I have NULL or empty values on my queries and also we will follow an approach that was commonly used by new users.

I do not think we should force the IS NULL search if an attribute has null value. For that purposes we already have yii\db\Expression don't you agree? I think it makes more sense and is less error prone (in terms of search results).

$query->where(['email' => yii\db\Expression('IS NULL')]);

[creocoder]

@tonydspaniard Absolute agree. Moreover this will make doing IS NULL and NOT IS NULL similar way, even simplier than using \yii\db\Expression for that. Like:

$query->where('some_id IS NULL');
$query->where('some_id IS NOT NULL');

And NULL or empty values should be removed from the resulting Query

I think you want to say should be ignored. For example when we do:

$query->where(['like', 'something', null]);
$query->where('something' => null);

This just should be ignored, e.g. nothing should be added to WHERE part of selection. From practical point of view this a lot of better behavior than currently. For me your approach having pros without any cons if compare with current approach. So, vote to do that change.

[qiangxue]

What about empty strings? Or a string of blanks?

[creocoder]

@qiangxue I'm sure that:

$query->where('something' => '');

should create WHERE something = ''

Not sure about:

$query->where('like', 'something', '');

should create WHERE something LIKE '%%'

[creocoder]

@qiangxue Hmm... there is an additional suggestion for where like syntax:

$query->where('like', 'something', 'test'); // LIKE 'test';
$query->where('like', 'something', '%test'); // LIKE '%test';
$query->where('like', 'something', 'test%'); // LIKE 'test%';
$query->where('like', 'something', '%test%'); // LIKE '%test%';

To make things more straighter. Than i think question with empty string will solve with like too:

$query->where('like', 'something', ''); // LIKE '' which make sence, select all record where something is empty string

[creocoder]

@qiangxue Btw you know that in mysql for example queries like LIKE 'TeSt' and = 'TeSt' can work differently depends on table settings. Currently its not possible to make query like LIKE 'TeSt'.

[tonydspaniard]

What about empty strings? Or a string of blanks?

If that is required, then we should consider only NULL to be ignored. I still believe that we should not reinforce the rule of NULL attributes are IS NULL and operator, operand to throw errors if NULL

[creocoder]

@tonydspaniard Yes, current nulls logic is bad from practice point of view. I think about @qiangxue about empty string more and decide make list for all changes we can do to make where better:

1) Ignore null values 2) Change where like logic from $query->where('like', 'some', 'some') generate LIKE '%some%' there reason is to impossible to generate LIKE 'some'. Why we may want LIKE 'SoMe' instead of = 'SoMe' is because it have difference behavior in sql. Queries like 'LIKE '%some%'' can be done through $query->where('like', 'some', '%some%') which is even more straighter 3) About empty strings. I think its good idea to make $query->where('like', 'some', '') ignored to while query like $query->where('some' => '') should not be ignored. The reason is = '' can replace LIKE '' easily while having $query->where('like', 'some', null) and $query->where('like', 'some', '') to be ignored is very interesting from practical point of view.

After that changes we will get more logical where synax. Note we do not loose anything. IS NULL or NOT IS NULL from that point can be reached consistent way by $this->where('some IS NULL') or $this->where('some IS NOT NULL').

I hope i maked good explaination of wanted behavior. @tonydspaniard Right?

[qiangxue]

@creocoder Take a look at this comment for the like syntax: https://github.com/yiisoft/yii2/blob/master/framework/db/Query.php#L388

The issue that @tonydspaniard raised here is about special handling of "empty" values when they are used in query conditions, which is purely from practical consideration.

There are a few kinds of "empty" values: nulls, empty strings, strings with all blanks. If we want to fix the null problem, we should also fix the string problem. Otherwise, we still need to repeat the same addCondition code.

IMO, generating name IS NULL from ['name'=>null] makes a lot of sense to me and is not totally useless in practice, although we also have very good reason that we want to ignore this condition in many scenarios. I'd like to see if we have other solutions to the problem.

[creocoder]

@qiangxue

If we want to fix the null problem, we should also fix the string problem.

Yes, you are totally right and suggestion how to fix it in mine above comment.

[qiangxue]

After that changes we will get more logical where synax.

I don't quite like your proposed solutions as they contain many special rules which may confuse users and may even bring in security problem (e.g. you require a search parameter from user, but you don't know that certain values will be ignored and as a result you are returning data to users without knowing that.)

Regarding the like operator, please take a look at the doc I listed. It's possible to generate what you want. (also note, LIKE expression needs to take care of special characters. It's not as trivial as you propose here.)

[creocoder]

@qiangxue

Take a look at this comment for the like syntax: https://github.com/yiisoft/yii2/blob/master/framework/db/Query.php#L388

Yes, looked. Found there another trouble. You never want LIKE foo = '%test1%' AND bar = '%test2%' AND baz = '%test3%'. Instead of it you want LIKE foo = '%test1%' OR bar = '%test2%' OR baz = '%test3%' in 99% cases. So this is another trouble in where syntax design.

[qiangxue]

There's OR LIKE operator.

You never want LIKE foo = '%test1%' AND bar = '%test2%' AND baz = '%test3%'. Instead it you want LIKE foo = '%test1%' OR bar = '%test2%' OR baz = '%test3%' in 99% cases

This is not quite true. For myself, when I search for something, I normally would want all my keywords matched. Otherwise it often would end up with too many results. Anyway, there's OR LIKE if you don't like this behavior.

[creocoder]

@qiangxue Ok, like syntax is another topic. Do you have any suggestion about how to deal with empty strings?

[qiangxue]

On the one hand, I don't quite like the fact that we are repeating addCondition() in every search model. On the other hand, I also don't quite like the special handling proposed here. I don't have a good solution in mind...

[creocoder]

@qiangxue What i really don't like that addCondition() not solve all troubles and we have an mess like:

    public function search($params)
    {
        ...
        $this->addCondition($query, 'id');
        $this->addCondition($query, 'title', true);
        $this->where(['between', 'sum', 0, 10]);
        ...
    }

The goal is to illiminate addCondition() and use where() always to not have mess addConfition() with where() inside one method.

[creocoder]

@qiangxue At least simple nulls ignoring will help to not use addConfition() at all. And make where() similar to Yii1 CDbCriteria::compare() method logic.

[qiangxue]

We have use cases for both scenarios (ignoring null/empty strings, NOT ignoring null/empty strings). I don't think there will be a clear call.

$query->where(['name' => null]);
$query->where(['name' => '']);

[creocoder]

@qiangxue I'm sure $query->where(['name' => '']); should produce WHERE name = '' and there is no other options.

[qiangxue]

...similar to Yii1 CDbCriteria::compare() method logic.

Yes, what I was thinking is to introduce a new method within Query that behaves similar to addCondition(). This will leave where() intact.

[qiangxue]

As we already concluded, you can't just handle nulls as it won't let you get rid of addCondition().

[creocoder]

@qiangxue New method is good idea too. I understand that current where() logic is acceptable. All trouble around lack of CDbCriteria::compare() alternative in Yii 2.

[qiangxue]

Any suggestion of the name? It needs to avoid confusion with where() and should be self-explanatory.

[creocoder]

@qiangxue Query::compare() ? ;)

[qiangxue]

filter(), filterBy(), or addFilter()?

[creocoder]

@qiangxue As i note Yii 2 trend is to use simple short names. I vote for Query::filter().

A little offtopic: Yesterday i was surprised when meet ActiveRecord::isRelationPopulated() instead of ActiveRecord::hasRelated(). Yes i understand that having is prefix convention for boolean returning methods is simple, but i do not think having has prefix as addition too make sence.

[qiangxue]

Perhaps we need three methods: filter, andFilter, orFilter. What parameters can these methods take?

but i do not think having has prefix as addition too make sence.

I don't quite understand you... Anyway, hasRelated doesn't sound quite right in grammar.

[qiangxue]

@Ragazzo: guess you may also have some input regarding the filter design since you did this with debugger improvement.

[slavcodev]

I think new methods (filter, etc) is unnecessary. I propose next approach, now we have (from doc)

- hash format: `['column1' => value1, 'column2' => value2, ...]`
- operator format: `[operator, operand1, operand2, ...]`

In hash format

where(['attr1' => null, 'attr2' => '']); // generate WHERE attr1 IS NULL AND attr2 = ''

In operator format

where(['attr1', null]); // skip condition
where(['is', 'attr1', null]); // WHERE attr IS NULL
where(['attr2', '']); // WHERE attr2 = ''
where(['like', 'attr1', null]); // skip condition
where(['like', 'attr1', '']); // WHERE attr1 LIKE '%%'

[tonydspaniard]

Yes, what I was thinking is to introduce a new method within Query that behaves similar to addCondition()

This would be less painful that fixing all methods @slavcodev .Moreover, the operator format you suggest could lead into confusion: some are skipped, others converted into IS NULL.

I vote for the change as @qiangxue stated, one function wont harm the rest of the code and will already overcome the actual logic issue.

Perhaps we need three methods: filter, andFilter, orFilter.

Not sure why the andFilter and orFilter, we could fix that issue with a parameter, but at the same time I like the wording here as it looks more practical.

[slavcodev]

I do not see confusion. In hash format, similar findByAttributes in Yii1, will generate IS NULL In operator format, IS NULL is generated only if it explicit write in code where(['is', 'attr1', null]); where(['is not', 'attr1', null]); In other cases, ignore the condition

In my opinion is very convenient. If you think the new methods which duplicate most of the code from where() better, well so be it. But if you are afraid to rewrite a lot of methods, do not forget Yii2 are still in alpha

[qiangxue]

@slavcodev Your proposal doesn't solve the empty string problem - we want to ignore empty string comparison in some (not all) cases. Also, your proposal put attribute in the same place as operand, which could cause conflict/confusion.

[slavcodev]

@qiangxue To solve issue with conflict/confusion, may change where(['attr1', null]); to where(['=', 'attr1', null]); To solve empty string, we can add new bool param

where(['like', 'attr1', '']); // WHERE attr1 LIKE '%%'
where(['like', 'attr1', '', true]); // skip condition

I am convinced that the new methods are not needed.

[qiangxue]

What about strings of blank spaces? For like operator, we already use the additional boolean parameter.

[slavcodev]

I do not understand your question, can you explain it?

[qiangxue]

I do not understand your question...

Never mind. Not a real question.

So as proposed by @slavcodev, an alternative solution is to introduce new operators to the existing condition expressions. For example, the = operator will only handle comparisons of values that are neither null nor empty; the ~ operator is similar but does LIKE comparison. We leave all existing operators unchanged. What's your opinion on this solution?

[samdark]

I don't like custom syntax proposal. Especially ~ because it is already used: http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html#operator_bitwise-invert

[samdark]

What's wrong exactly with

!empty($this->full_name)) ?: $query->where(['like', 'full_name', $this->full_name];

?

[tonydspaniard]

For example, the = operator will only handle comparisons of values that are neither null nor empty; the ~ operator is similar but does LIKE comparison

Looks good to me. That will solve the creation of new function and there won't be major changes to the where condition. Will have to be clearly documented. I can see lots of people trying to do things like Yii1, and by using Yii2 on an internal project, I already realized that we need to change our minds on new approaches.

[tonydspaniard]

What's wrong exactly with

Do we really have to use !empty for each model's attribute we wish to search for? What if the model has more than 30+ attributes. Do you still think is right to check for each attribute on the search function? (assume we need to search all fields)

Edit: Agree with @samdark on the ~ operator. Maybe we need to think for a different symbol ?

[slavcodev]

Please notice, I don't propose custom operators, I don't like this idea too.

I propose the compromise. Change behavior of query to:

// in hash format generate correct condition, similar findByAttributes from Yii1
// want skip null and empty string, use operator format
$query->where(['email' => null]); // generate WHERE `email` IS NULL

// in operator format null value and empty string ignored
$query->where(['=', 'email', null]); // skip condition, notice `=` is real operator, maybe `!=`, `<` or `>`
$query->where(['like', 'full_name', '']); // skip condition

// need empty string condition, use explicit string condition
$query->where('full_name like %?%', ['']);
$query->where('email = "?"', ['']);
$query->where('email IS NULL');

[samdark]

Custom operator may match one of operators of backend so it's not a good idea to introduce it.

[qiangxue]

Custom operator may match one of operators of backend

@samdark What's the problem with this?

I propose the compromise. Change behavior of query to:

@slavcodev So in summary, you propose this?

• introduce the = operator which does equality comparison but will skip the condition if the value being compared is null or an empty string;
• specially handle like operator: when the value being compared is null or an empty string, ignore it. What about not like and other similar like operators?

[samdark]

@qiangxue confusion. If I know MySQL (or any other backend) well I may be confused by a very familiar character.

[slavcodev]

@qiangxue

• the = not introduced, it is real operator from backend, and used in query, it maybe any operator, ie !=, <, >=
• for all operators in operator-format (like, not like, in) when the value is empty (null, empty string, empty array), ignore it
• when need operating condition for empty values, need to use string-format where('attr = ?', [$emptyArray]), where('attr IN()', [implode($emptyArray)]), where('attr IS NULL')

[creocoder]

@qiangxue After some thinking i come to conclusion that there is no empty strings trouble. We can solve empty strings trouble with default validator. After that all empty strings will become nulls. How about just make simpe rule "ignore when operand(s) is null". I think we will get what we want. Without introuducing new method. Without new doubtful new operators like "=", "!=", etc.

[Ragazzo]

@qiangxue hah, very long discussion, but i would rather say no then yes to the supposed changes because of IS NULL is much more better then just ignoring this case, also addCondition is working well, i see no real benefits from doing as @tonydspaniard suggested. The only thing we will have after accepting all this changes is just unclear db-command behavior, Yii2 already have other features that are not so good. So i dont think that this should be accepted. Its fine as is, i dont like when developers starting to push some view-layer based features to the persistence layer like in this case.

[creocoder]

@Ragazzo addCondition() in every search model is excess method. Also if you read topic from start and understand what troubles we try to solve you will understand that addCondition() is not enough in real-word applications and 99% there is mess from addCondition() and where() with excess condition wrappers...

[Ragazzo]

addCondition() in every search model is excess method.

just dont copy it, get some base-search-model.

is not enough in real-word applications and 99% there is mess from addCondition() and where() with excess condition wrappers...

i am about that if i pass value that is null or empty it should generate correct conditions and not just throw it away. If needed i will do filtering before passing value.

[creocoder]

just dont copy it, get some base-search-model.

Thanks, i do not want have silly base model for addCondition() purposes and as i say earlier addCondition() is just a hack to avoid uncomfortable where() logic. There is no other roles for addConfition(). This looks like we make troubles to reach them ;) So topic about make where() usable in every case without need to wrap it inside condition every time.

[Ragazzo]

Thanks, i do not want have silly base model for addCondition()

But huge number of scenarios is just fine? right? ;)

This looks like we make troubles to reach them ;) So topic about make where() usable in every case without need to wrap it inside condition every time.

no, the topic is about ignoring user, and deciding what is needed by him without even asking him. Good way to hell :)