how to check if a column has truthy value using laravel's eloquent-CodePudding

Suppose I have Post model that has is_verified column with smallint datatype, how can I get all records that is verified? One thing to do this is using this:

Post::where('is_verified', true)->get();

The code above will produce the following query:

select * from `posts` where `posts`.`is_verified` = true

... which will get me all verified Post records; in note that is_verified on all existing records is either 0 or 1.

However, after I get myself curious and try to manually change some is_verified's record value from 1 to another truthy number e.g. 2, the above eloquent query didn't work as expected anymore: records with is_verified value of 2 didn't get retrieved.

I tried to execute the sql query directly from HeidiSQL as well, but it was just the same. Then I tried to change the = in the sql query to is, and now it's working as expected i.e. all records with truthy is_verified get retrieved:

select * from `posts` where `posts`.`is_verified` is true

So my questions are:

Does the above behaviour is correct and expected?
How can I execute the last sql query in eloquent? One thing I can think of is where('is_verified', '!=', 0) but that feels weird in terms of readability especially when the query is pretty long and a bit complicated
As I stated before, the is_verified column is a smallint. Does this affects the behaviour? Because this conversation here states that boolean column datatype is typically tinyint, not smallint.

And that's it. Thank you in advance!

CodePudding user response：

It is not the correct way to handle boolean values, you shouldn't save boolean columns as smallint, you can use the explicit boolean column type as described in the documentation.
Once you setup the boolean field correctly the logic you have in place will work. So Post::where('is_verified', true)->get(); will return the expected results.
Yes, the problem is the smallint column type, if you put tinyint it also should work like the boolean column. You can read more about the differences here.

CodePudding user response：

After doing some deeper digging, I would like to write down the things I've found:

I have updated my mysql to the newest version as of now (v8) and boolean datatype defined in migration results in tinyint(1) in the db. This is happening turns out because in mysql bool or boolean are actually just the synonyms or tinyint(1), so that was a totally normal behaviour, not due to lower-version issues.
I found @dz0nika answer that states that smallint and tinyint results in different behaviour in the query to be quite incorrect. The two datatypes simply differ in terms of byte-size while storing integer value.
As of mysql documentation, it is stated that:

A value of zero is considered false. Nonzero values are considered true.

But also that:

However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively.

Meaning that:
```
select * from `posts` where `posts`.`is_verified` = true;
```
Is the same as
```
select * from `posts` where `posts`.`is_verified` = 1;
```
Thus the query will only get Post records with is_verified value of 1.

To get Post records with truthy is_verified value, wether 1, or 2, or 3, etc; use is instead of = in the query:
```
select * from `posts` where `posts`.`is_verified` is true;
```

You can read more about these informations here and here (look for the "boolean" part)

So, how about the eloquent query? How can we get Post with truthy is_verified using eloquent?

I still don't know what's best. But instead of using where('is_verified', '!=', 0) as I stated in my question, I believe it's better to use selectRaw() instead:

Post::whereRaw('posts.is_verified is true')->get();

If you found this information to be quite missing or incorrect, please kindly reply. Your opinion is much appreciated.