Failing searches, caused by null-able date fields [message #1367] |
Tue, 10 June 2008 09:24 ![Go to next message Go to previous message](/theme/default/images/down.png) |
ikatz
Messages: 40 Registered: December 2007 Location: Durham, NH
|
Member |
|
|
I was seeing some very odd search behavior, which caused most of my searches to return 0 results ("Nothing was found matching this profile"). The behavior was really confusing because some of the searches would return results the first time, but clicking "search" a second time and submitting the same search criteria would return no results.
By logging the SQL queries, I was able to determine that my problem is being caused by 2 date fields in this particular table which can be null. Radicore is inventing search criteria from the date fields, even when I leave them blank.
Note: The table I am working on here is called task, and is part of a project manager I am working on -- not the Radicore menu system.
SELECT SQL_CALC_FOUND_ROWS task.*,
project.name as project_valrep,
parent_task.name as parent_task_name
FROM task
LEFT JOIN project
ON (project.project_id=task.project_id)
LEFT JOIN task AS parent_task
ON (parent_task.task_id=task.parent_task_id)
WHERE task.name LIKE 'fred%'
AND task.start_date<='2008-06-10 00:00:00'
AND task.end_date>='2008-06-10 23:59:59'
LIMIT 10 OFFSET 0
Start_date and end_date are my null-able fields. At first I thought that Radicore was simply generating default conditions on the date fields, since both were null. But what is really confusing to me is the fact that the 2 conditions are different
AND task.start_date<='2008-06-10 00:00:00'
AND task.end_date>='2008-06-10 23:59:59'
Are task.start_date and task.end_date reserved in Radicore? How can I prevent these conditions from being added to the search criteria?
|
|
|