Get User by attribute
I added some additional user attributes via the user.xml
file.
As far as I know it is not possible to get a User by one of these custom properties, right?
So I can do:
Users\Manager->get()
Users\Manager->getUserByMail()
Users\Manager->getUserByName()
But it's not possible to do something like:
Users\Manager->getByAttribute('my.custom.attribute', 123)
to get the user having the attribute my.custom.attribute
with a value of 123
.
I know these attributes are stored as JSON in a single database field so it's not easy/possible to query them.
But recently I found out about MySQL supporting JSON and having some handy JSON functions (such as JSON_EXTRACT
). These functions would make it possible to query JSON data as well.
For example:
If have the following data in my table:
id | data |
---|---|
1 | {"my.attribute": false, "some.other.attribute": "blargh"} |
2 | {"my-attribute": false, "some.other.attribute": "blargh"} |
3 | {"my-attribute": true, "some.other.attribute": "blargh"} |
Using this query
SELECT * FROM my_table WHERE JSON_EXTRACT(data, '$."my.attribute"') = true;
Returns:
id | data |
---|---|
3 | {"my.attribute": true, "some.other.attribute": "blargh"} |
Conclusion:
This means querying by attributes would be possible.
I didn't check the performance yet, but I read about MySQL being able to cache and optimize queries if you define keys (for/inside the JSON?).
I guess the performance would be even better if the type of the attribute column would be JSON
.
Only caveat is that the JSON functionality became part of MySQL in version 5.7 and I'm not sure which MySQL versions QUIQQER tries to support.
What do you think of integrating this into QUIQQER, @henbug (and maybe @mor)