Radicore Forum: How To » Failing searches, caused by null-able date fields

Home » RADICORE » How To » Failing searches, caused by null-able date fields

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Failing searches, caused by null-able date fields [message #1367]

Tue, 10 June 2008 09:24

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?

Report message to a moderator

Re: Failing searches, caused by null-able date fields [message #1368 is a reply to message #1367]

Tue, 10 June 2008 12:54

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

If a database table contains both a START_DATE and END_DATE field then when a search screen is initiated a field called CURR_OR_HIST is automatically added to the $fieldspec array, with the default value of 'C', to activate the feature described in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq53. If you do not want this feature then you can remove this CURR_OR_HIST field from the data which is returned by the search screen using code in the _cm_changeConfig() method such as:

if ($GLOBALS['mode'] == 'search') {
    unset($this->fieldspec['curr_or_hist']);
} // if

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Failing searches, caused by null-able date fields [message #1369 is a reply to message #1368]

Tue, 10 June 2008 14:37

ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH

Member

Perfect. Thanks!

While I'm thinking about it, is there a list somewhere of field names that Radicore treats specially?

[Updated on: Tue, 10 June 2008 14:38]

Report message to a moderator

Re: Failing searches, caused by null-able date fields [message #1370 is a reply to message #1369]

Tue, 10 June 2008 19:30

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

Take a look at http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq89.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Previous Topic:	Use update2 pattern when I have two tables parent->child
Next Topic:	Screen structure reference for filepicker-ed images

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Fri Nov 22 12:24:22 EST 2024

Total time taken to generate the page: 0.01034 seconds