Re: Simple General Search Qualifier
Re: Simple General Search Qualifier
- Subject: Re: Simple General Search Qualifier
- From: Peter Vandoros <email@hidden>
- Date: Tue, 22 May 2007 10:38:05 +1000
Hi,
What i have done is created a custom JDBC plug-in subclass of the
default MySQLPlugIn that generates the correct sql. I based mine off the
PostgreSQL plug-in in Project WOnder. From memory, i didn't have to
change much and everything just works.
Give that a go and let me know if you need help.
I have thought about contributing this back to WOnder but i need
permission to do that first. Unfortunately, i can't contribute that
until i get that permission.
Regards
Peter
Denis Frolov wrote:
Hi,
You probably should try using left outer join for the relationship,
although we had no luck with it for MySQL. Looks like the EOF sql
generation doesn't support outer joins with MySQL.
The same problem is discussed here:
http://wodeveloper.com/omniLists/eof/2002/October/msg00005.html
I would also really like to hear what is the recommended solution to
this problem.
Denis.
On May 19, 2007, at 8:02 PM, Kieran Kelleher wrote:
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?
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden
<mailto:email@hidden>)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden <mailto:email@hidden>
--
Denis Frolov
Design Maximum MA
Tel: +7 863 2648211
Fax: +7 863 2645229
Web: http://www.designmaximum.com
--
This message has been scanned for viruses and
dangerous content by *MailScanner* <http://www.mailscanner.info/>, and is
believed to be clean.
------------------------------------------------------------------------
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
--
Peter Vandoros
Software Engineer
Etech Group Pty Ltd
Level 3/21 Victoria St
Melbourne VIC 3000
Australia
Ph: +61 3 9639 9677
Fax: +61 3 9639 9577
----------------------------------
IMPORTANT: This e-mail message and any attachments are confidential and
may be privileged. If received in error, please reply to this message
and destroy all copies and any attachments. You should check this
message and any attachments for viruses or defects. Our liability is
limited to resupplying any affected message or attachments. For more
information about Etech Group, please visit us at
http://www.etechgroup.com.au.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden