Ruslan.Zakirov at acronis
Jan 14, 2005, 5:28 AM
Post #6 of 7
(1126 views)
Permalink
|
|
Re: Re: Custom Field Searching broken again?
[In reply to]
|
|
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
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Be sure to check out the RT wiki at http://wiki.bestpractical.com
|
smime.p7s
