[rt-users] Re: Custom Field Searching broken again?

[Ruslan U. Zakirov]

IMHO all joins should be LEFT. MySQL doesn't support FULL JOIN.

SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
     ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
     ObjectCustomFieldValues_1.ObjectId)) LEFT JOIN CustomFields 
CustomFields_2
     ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id))  WHERE
     ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
     ((main.Type = 'ticket'))
AND ((ObjectCustomFieldValues_1.Content IS NULL))


May be it can be automated in DBIx::SB or at least checked.
If we try NORMAL join into table1 that has restriction(in WHERE) 'IS 
NULL' and that table1 is itself LEFT or RIGHT joined into any table2, 
then it's wrong wish.
Next template:
... LEFT JOIN Table1 ON(...) JOIN Foo ON(Table1.Foo  ...) ....
Sorry... Looks like it's more complex than I thought.


But exactly this query(with LEFT JOINs) could be stripped to:
SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
     ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
     ObjectCustomFieldValues_1.ObjectId)))
WHERE
     ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
     ((main.Type = 'ticket'))
     AND ((ObjectCustomFieldValues_1.Content IS NULL))

Micah Cooper wrote:
> On Jan 13, 2005, at 4:57 PM, David Snyder wrote:
> 
>>> On Thu, Jan 13, 2005 at 04:25:41PM -0500, David Snyder wrote:
>>>
>>>> David Snyder wrote:
>>>>
>>>>> I've just installed 3.4.0rc5  (previously was testing rc2).  Is it
>>>>> just me, or is custom field searching still not working?
>>>>>
>>>>> David
>>>>
>>>>
>>>> I meant to include this from my apache error log.  This was when trying
>>>> to search for tickets where a global custom field contains "problem."
>>>> Also, I've got SearchBuilder 1.19
>>>
>>>
>>> What version of mysql are you running?
>>> If you run this command from the mysql commandline, what do you get?
>>>
>>>
>>> SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
>>>     ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
>>>     ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields 
>>> CustomFields_2
>>>     ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id))  
>>> WHERE
>>>     ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
>>>     ((main.Type = 'ticket')) AND ((ObjectCustomFieldValues_1.Content 
>>> LIKE
>>>     '%problem%'))
> 
> 
> I think I mentioned a while back that since upgrading (worked fine 
> before) to 3.4.0 and continuing with rc5, when I do a searching on 
> custom fields where the value IS (no value), I get 0 records returned. 
> I'm using PostgreSQL 7.4.6 on FC2/apache2/mod_perl (latest distro 
> versions), DBIx::SearchBuilder 1.19.
> 
> If the above represents the query that is run, did the second join 
> change from a full join?
> 
> This query returns 641 records:
> SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
>     ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
>     ObjectCustomFieldValues_1.ObjectId)) FULL JOIN CustomFields 
> CustomFields_2
>     ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id))  WHERE
>     ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
>     ((main.Type = 'ticket'))
> AND ((ObjectCustomFieldValues_1.Content IS NULL))
> 
> Thanks!
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Be sure to check out the RT wiki at http://wiki.bestpractical.com