I'm stuck with a very important solution and the answer to it must be really simple but I'm not seeing it. Please shed some light.
The problem is in my search engine when I'm looking for empty values in related records.
Say, the primary table is job and it has a relation to the job_type table.
A job record may or may not have a reference in job_type.
So for some jobs I'll have a related record and for others I won't.
When I execute the following code, I always get zero results:
- Code: Select all
jobs.find();
jobs.jobs_to_job_type.job_type_name = '^';
jobs.search();
I understand the logic behind it: there are no related records that have job_type_name equal to null (because if there is a related record, then it has some value, some job_type).
The problem is the same when my search operator is 'not equal to' or 'does not contain'.
Again, I understand how it is only logical but the users will see the empty job_type on the job form and wonder why they cannot see this job in the search results. For them, the job_type is an empty field on the job, they're not aware that it's not the job table and they don't care.
I've tried a few approaches where I change the find/search conditions and I've tried totally different approaches to finding data. Some worked quite nicely in fact, on a small database, but a large number of data is killing them and our clients will have hundreds of thousands records.
If there was a way for me to find all records in the primary table that don't have a related record in the other table, that would solve the problem.
Also, the search engine I developed is completely dynamic and fully customisable by the end user, they can add as many search conditions as they want from all related tables, use a dozen different operators, brackets, data types, boolean logic, etc. It is even doing the search on unstored calcs. So it is not a matter of putting up together a SQL query against the main table to find which records have an empty foreign key, it's fairly complicated and the solution must be generic.
There you go. I wonder how others deal with this issue, it must be fairly common.