CiviCRM Community Forums (archive)

I attempted to use the procedure here:

http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components

and it appears to mostly work, but not yet quite to the end.

I added:

echo $sql;die;

just before:

        return $sql;

the last line of "function all", which is line 133 in my custom file to confirm that the generated SQL is what I want. It seems to be, because if I run that SQL from the MySQL command line, I get back my desired results. When I remove that line and run the search in Drupal 5.7 using my CiviCRM 2.0.4, however, I get no results whatsoever--just the search page back. Here is my SQL:

SELECT civicrm_contact.first_name, civicrm_contact.last_name, civicrm_contact.id as contact_id FROM
civicrm_contact left outer join civicrm_value_1_SomeCustomField
on civicrm_contact.id=civicrm_value_1_SomeCustomField.entity_id
WHERE civicrm_contact.id=civicrm_value_1_SomeCustomField.entity_id is null

Can anyone shed any light on this? I attached my PHP file here, renamed to .txt for this forum to accept it.

Thank you!!

PS: I see that that page has this:

CRM_Core_Error::debug('sql',$sql);
   exit();

suggested for debugging, but anyhow that shows the same results as above.

> an equality check and an is null check? my sql is not very good, but is that right?

Excellent question. In this case, "right" is relative. Is this valid SQL? Yes, it is. Is it what I actually intended to write? No, it's not.

It's valid SQL because

WHERE civicrm_contact.id=civicrm_value_1_SomeCustomField.entity_id is null

is apparently the same as

WHERE (civicrm_contact.id=civicrm_value_1_SomeCustomField.entity_id) is null

and either actually runs fine from the command line. Selecting "Count(*) as c" with either of the above WHERE clauses yields 28338.

However, you are correct that this is causing the problem in CiviCRM because if I edit my SQL to remove the "civicrm_contact.id=" from the WHERE clause, then I do get results. But, I only get 11.

So the SQL I see when I use echo is now:

SELECT civicrm_contact.first_name, civicrm_contact.last_name, civicrm_contact.id as contact_id FROM
civicrm_contact left outer join civicrm_value_1_SomeCustomField
on civicrm_contact.id=civicrm_value_1_SomeCustomField.entity_id
WHERE civicrm_value_1_SomeCustomField.entity_id is null

and running that in the command line produces results, and using Count(*) still shows 28338 from the command line.

BUT, when I run the search in CiviCRM, I get 11 results. Something else is a bit odd as well. Via the MySQL command line, using the SQL, the first ids i see returned are 2,3,6,8 but in CiviCRM the first are 1,5,2562. The first few anyhow seem entirely disparate between these two lists.

To begin to debug I changed my where clause to:

        $select  = "
count(*) as contact_id
";

and the one contact_id returned is 28338, and it says "Found 1 contact" (that part is expected of course). That value 28338 is correct, but with the original SQL I get 11 contacts, not 28338.

Now this is quite odd I think. So I tried just:

        $select  = "
civicrm_contact.id as contact_id 
";

and I get the same "Found 1 contact" with id 28338. In fact, now if I put the WHERE clause back to what it was originally, in my attached file below, I still get "Found 1 contact" with id 28338. Now am I quite lost.

Any further ideas how I can debug this?