Thank you Jesse and Michael,
I’ll share the solution we came up with :-)
We have a “user search” tool were we can dynamically fill out and query for
users in the system with various checks such as obvious things like first and
last name but also by their roles, whether they are expired or not, and also
their email address.
We wanted a new checkbox to look for “non-standard” email addresses. Weird
characters, or at least ones we think are suspicious like % and * (percent and
asterisk). Things we don’t like shouldn’t be let in but there is also very old
data and a desire to clean it up.
If we want a qualifier to search for asterisk, we might naively attempt using
Wonder ERXKeys like so:
===
EOQualifier qualifier = User.PERSON.dot(Person.EMAIL).contains("*")
The “contains” method guts are the following:
===
public static ERXKeyValueQualifier contains(String key, String value) {
value = "*" + value + "*";
return ERXQ.likeInsensitive(key, value);
}
So we are effectively doing a case-insensitive-like on triple-asterisk which
yields all records. The asterisk in WO is like percent for regular SQL, it gets
translated so * becomes % when it hits the DB.
As far as I know, there is no way to escape an asterisk. Can’t be done! But
hope is not lost, when you meet a brick wall, walk around it.
I dug the raw SQL qualifier out of the basement. NeXT/Apple provides it, it is
deprecated, but useful when you are in a pickle.
You would do something like this:
===
EOEntity userEntity = EOUtilities.entityNamed(editingContext(),
User.ENTITY_NAME);
EOQualifier qualifier = new EOSQLQualifier(userEntity,
User.PERSON.dot(Person.EMAIL).key() + " like '%%*%%'", null)
WARNING: you might think there should be a single percent (%) but that would
lead to errors because that is a qualifierFormat string and you typically
provide variables for replacement with %@ or if you want to be clear you would
do %s for a string, %d for an int, and %f for a float/double. Then where I
passed in “null” you would instead pass in an array of values to use for
replacement. You have to escape the percent and to do that you double it as %%
In context the code could be crafted like so:
===
EOEntity userEntity = EOUtilities.entityNamed(editingContext(),
User.ENTITY_NAME);
EOQualifier otherThanStandardCharactersQualifier = ERXQ.or(
User.PERSON.dot(Person.EMAIL).contains("#"),
User.PERSON.dot(Person.EMAIL).contains("$"),
User.PERSON.dot(Person.EMAIL).contains("%"),
… other similar checks
new EOSQLQualifier(userEntity, User.PERSON.dot(Person.EMAIL).key() + "
like '%%*%%'", null),
new EOSQLQualifier(userEntity, User.PERSON.dot(Person.EMAIL).key() + "
like '%%?%%'", null)
);
… do a fetch using EOF.
What gets generated by EOF into SQL looks something like:
===
SELECT *
FROM
user t0
INNER JOIN person T1 ON t0.person_id = T1.id
WHERE
UPPER(T1.email) LIKE UPPER('%#%' :: varchar(255)) ESCAPE '|'
OR
UPPER(T1.email) LIKE UPPER('%$%' :: varchar(255)) ESCAPE '|'
OR
UPPER(T1.email) LIKE UPPER('%|%%' :: varchar(255)) ESCAPE '|'
OR
T1.email like '%*%'
OR
T1.email like '%?%'
;
Note #1: look at how regular qualifiers always produce something that is
escaped with a pipe symbol "|" even when they have nothing to escape.
Note #2: when doing a contains("%") it does escape it using a pipe in the
generated output and makes sense.
Note #3: The last two lines are from our raw SQL qualifier but it was cool that
it figured out how to follow the key path and use the join appropriately then
only using T1.email where its needed. Pretty smart. I was surprised it was able
to do that.
Note #4: We could use the raw SQL qualifier to do things that EOF has no way of
supporting like regular expressions.
But now we’d have two problems:
https://blog.codinghorror.com/regular-expressions-now-you-have-two-problems/
How to do regex in Postgres:
https://www.sqlshack.com/working-with-regular-expressions-in-postgresql/
https://www.postgresql.org/docs/current/functions-matching.html
Note #5: I also added the question mark because there is an undocumented
feature of EOF that would otherwise treat it like a wildcard courtesy of “he
who will not be named” (a NeXT engineer).
Cheers,
— Aaron
> On Oct 12, 2023, at 12:52 PM, Michael Kondratov <mich...@aspireauctions.com>
> wrote:
>
> I think you can pass it as a variable to a fetch specification.
>
>
> Sent from my iPhone
>
>> On Oct 12, 2023, at 10:45, Aaron Rosenzweig via Webobjects-dev
>> <webobjects-dev@lists.apple.com> wrote:
>>
>> Hi all,
>>
>> We can do a case insensitive like with EOF for *blue* on a text field (to
>> find “blue” at the beginning, middle, or end of a field)
>>
>> But what if we want to find an asterisk anywhere in a text field? How do we
>> do a case insensitive like looking for an asterisk anywhere in the text? Is
>> this possible with WO or do we need to resort to a raw SQL qualifier?
>>
>> Thanks in advance,
>> — Aaron
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/michael%40aspireauctions.com
>>
>> This email sent to mich...@aspireauctions.com
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
