Has anyone got a generic search qualifier construction utility method that takes an array of key/keyPaths of text attributes to search, a search term and will take into account that some objects in the keyPath relationships may be null. A simple EOOrQualifier of EOKeyValueQualifiers does not do it for the simple reason that null relationships on the keypaths get excluded as a result of the SQL generated.
For example, here is an array of key/keypaths to search (using * + searchTerm + * and an EOOrQualifier from array of EOKeyValueQualifiers with case insensitive like on keys/keypaths: contactFirstName,contactLastName,emailAddress,traderCode,businessName,ownerUser.firstName,ownerUser.lastName,ownerUser.emailAddress,ownerUser.comment,address.street1,address.street2,address.city,address.state,address.zip
The address and ownerUser relationships are optional, so a term that matches the traderCode attribute for example will not select the object of the ownerUser is null, but will if ownerUser is not null. The SQL generated using simple EOKeyValueQualifier array and EOOrQualifier is like this for the example above and the final 'AND t0.oidowneruser = T1.oid' obviously excludes records where t0.oidowneruser is null:
SELECT t0.businessname, RTRIM(t0.cellphone), t0.contactfirstname, t0.contactlastname, t0.contacttitle, t0.defaultcommission, t0.defaultfixedamountcommission, t0.emailaddress, t0.enddate, RTRIM(t0.fax), RTRIM(t0.isnotificationson), t0.oid, t0.oidaddress, t0.oidowneruser, t0.oidreseller, RTRIM(t0.phone), t0.recaddtime, t0.recadduser, t0.recmodtime, t0.recmoduser, t0.startdate, t0.tradercode, t0.type FROM trader t0, address T2, usr T1Â WHERE (t0.type = 20 AND (UPPER(t0.contactfirstname) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(t0.contactlastname) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(t0.emailaddress) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(t0.tradercode) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(t0.businessname) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T1.firstname) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T1.lastname) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T1.emailaddress) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T1.comment) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T2.street1) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T2.street2) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T2.city) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T2.state) LIKE UPPER('ËUF%') ESCAPE '|' OR UPPER(T2.zip) LIKE UPPER('ËUF%') ESCAPE '|')) AND t0.oidaddress = T2.oid AND t0.oidowneruser = T1.oid;
Anyone done this already and has code to share please? |