Tricky SQL for a smart search

Question

I am using a form through a PHP CMS system, that defines custom fields and such.

One of the custom fields allows for an input field which does a smart search. What this means is that when you start typing, it shows the records that match, quite similar to google suggest.

The smartsearch input field relies on a stored mysql search, and this is what I am having trouble with, because it just looks too complex.

I am going to paste the SQl from an existing search, and then explain what I am trying to do.

There are different sql queries in a few columns, as follows:

fromclause

((`clients` INNER JOIN `addresstorecord` 
    ON `clients`.`uuid` = `addresstorecord`.`recordid` 
    AND `addresstorecord`.`tabledefid`='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' 
    AND addresstorecord.primary='1') 
INNER JOIN `addresses` ON `addresstorecord`.`addressid` = `addresses`.`uuid`)

displayfield:

IF(clients.company != '', 
   CONCAT(clients.company,
          IF(clients.lastname != '' OR clients.firstname != '', 
             CONCAT(' (',
                    IF(clients.lastname != '', clients.lastname, '{blank}'),
                    ', ',
                    IF(clients.firstname != '', clients.firstname, '{blank}'),
                    ')'),
             '')
          ),
   IF(clients.lastname != '' OR clients.firstname != '',
      CONCAT(IF(clients.lastname != '', clients.lastname, '{blank}'),
             ', ',
             IF(clients.firstname != '', clients.firstname, '{blank}')),
      ''))
)

secondaryfield:

IF(addresses.city != '' OR addresses.state !='' OR addresses.postalcode != '',
  CONCAT(IF(addresses.city != '', addresses.city, ''),
         ', ',
         IF(addresses.state != '', addresses.state, ''),
         ' ',
         IF(addresses.postalcode != '', addresses.postalcode, '')),
  'unspecified location')

classfield

clients.type

searchfields

clients.company, clients.firstname, clients.lastname

filterclause

clients.inactive=0

I am having trouble understanding how these queries actually work. displayfield and secondaryfield in particular seem very redundant.

i don´t think my needs are so different from how the current example smartsearch field works...just instead of clients i want guests, and instead of address, i just want it to match firstname, lastname or passport number.

I wonder if I even need secondaryfield in that case?

In particular, the inner join in the fromclause confuses me, as I don´t think I need to do that as all the guest information is in one table...

Any assistance here is much appreciated, thankyou.

Answer 1

I reformatted your code samples with some indenting so it's easier to read. They were very long and scrolling horizontally it's hard to tell what's going on.

Something like this appears to do nothing:

IF(addresses.state != '', addresses.state, '')

I would guess it was written by someone who didn't understand how NULL works in SQL, or someone who is accustomed to Oracle, where NULL and '' are equivalent.

Displayfield and secondaryfield have different content. Displayfield shows the person's company and name, whereas secondaryfield shows the person's address, state, and postal code. These are not redundant.

The complexity comes from trying to handle blank or unspecified content.