How do I search for nulls in related foundsets?

Questions, tips and tricks and techniques for scripting in Servoy

How do I search for nulls in related foundsets?

Postby maria.kyselova » Mon Mar 31, 2014 1:59 am

Hi guys,

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.
Cheers,
Maria
maria.kyselova
 
Posts: 172
Joined: Thu Aug 09, 2012 12:56 am

Re: How do I search for nulls in related foundsets?

Postby Harjo » Mon Mar 31, 2014 9:26 am

your relation: jobs_to_job_type has an pk(leftside) and fk (right side)
let's say your pk name is: job_type_name_id

than do the search:

jobs.find();
jobs.job_type_name_id = '^';
jobs.search();
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: How do I search for nulls in related foundsets?

Postby maria.kyselova » Mon Mar 31, 2014 11:46 pm

Harjo wrote:your relation: jobs_to_job_type has an pk(leftside) and fk (right side)
let's say your pk name is: job_type_name_id

than do the search:

jobs.find();
jobs.job_type_name_id = '^';
jobs.search();


Hi Harjo,

I've tried that.
My other relations have multiple items, some of them may or may not be a pk or a fk.
Say, I am connecting my job table with employee table. Here I have a relation for the employee on the job.
The relation has two items: employee.employee_id = job.employee_id and employee.is_active = globals.trueValue.
Which item do I use to get the pk name? Some relations have more than two items, some are linked to global variables or foreign keys, etc.
I've tried this and, unfortunately, it wouldn't quite work.

To make things more complicated, I have 2 references from my job table to employee: one is for the employee assigned to do the job and the other is for the salesperson on the job (who happens to be an employee, too). The foreign keys in the job table are employee_id and employee_id_salesperson. So even if I have a naming convention to get the foreign key name from the relation's primary and foreign tables, I'm unable to use it because I don't know if I'm searching for a salesperson or job employee (this sounds a little crazy at this point, but trust me, it makes sense when you bury your head in it).

When I'm dynamically handling these cases (and I have thousands of combinations), it is hard to figure it out, so unfortunately .

I came up with a solution but it's very slow at the moment.
If I need to find records with empty related field, I run a search for non-empty values (so, where column is not null), then invert the result foundset.
However, I need to do the same thing if I'm looking for records where a field IS NOT EQUAL TO a value as part of the search - because the normal Servoy '!' operator does not include records that don't have a relation in the first place. So I run the first search to find all records whose 'relatedField is not equal to 'blah'', then I store the pk's of the result in an array, then I run a separate search to find all records whose 'relatedField is null' (find non-empty values, invert foundset). Then I store the second search result pk's in an array and then I merge both array so I don't have duplicates.

On top of that, if this 'IS NOT EQUAL' condition is following some other search condition and is AND'ed to it, then I need to include the result records from that previous condition, so I merge the arrays from above with the results of my first condition and that involves converting foundsets into datasets, then to arrays, then back to datasets and loading datasets into foundsets.

You can see, there is a lot of conversion from foundsets to datasets and back - this is what slows things down considerably if the table has 3,000 records. A simple 'NOT EQUAL TO' search takes 3 seconds, way too long.

All because there is no built-in mechanism to find nulls in related foundsets (well, to find records that don't have a related record in that table).

I don't believe anyone else hasn't faced the same problem yet.
Would Servoy think about introducing this feature in their search?
Cheers,
Maria
maria.kyselova
 
Posts: 172
Joined: Thu Aug 09, 2012 12:56 am

Re: How do I search for nulls in related foundsets?

Postby Harjo » Tue Apr 01, 2014 9:30 am

I think your problem is too complicated to get an (out of the box) answer. :wink:
It depends on so many thanks.

Maybe you can shrink it down into a small sample-solution and post it here?
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: How do I search for nulls in related foundsets?

Postby jasantana » Tue Apr 01, 2014 10:19 am

Hi Maria, I see the complication in your process, what I would do is load a dataset by query and then load the foundset from resulting dataset.

The key is write the proper SQL. I presume you are using postgreSQL so I would try to write your SQL in pgAdmin and then execute it from Servoy.

Cheers,
Best regards,
Juan Antonio Santana Medina
jasantana@nephos-solutions.co.uk
Servoy MVP 2015
Servoy 6.x - Servoy 7.x - Servoy 8.x - MySQL - PostgreSQL - Visual Foxpro 9
User avatar
jasantana
 
Posts: 555
Joined: Tue Aug 10, 2010 11:40 am
Location: Leeds - West Yorkshire - United Kingdom

Re: How do I search for nulls in related foundsets?

Postby maria.kyselova » Wed Apr 02, 2014 1:36 am

Thanks, guys.
I had a second look at the QueryBuilder and it seems to do the job for me.
Had to re-write my search engine core, but it's much more efficient now.
The only problem is with ANDs and ORs, can't seem to figure out certain combinations, like 'SELECT * FROM table LEFT JOIN .. LEFT JOIN.. LEFT JOIN.. WHERE a AND b OR c'

'WHERE a OR b AND c' works, all OR's work, but the one above doesn't.

Perhaps, someone could help me out here? Here is an example that gives me the right results (WHERE a OR b AND c):

Code: Select all
query.where
            .add(query.or
               .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_department.columns.departmentid.eq('SUPPORT'))
               .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_statuscode.columns.statuscodeid.eq('COMMENCED'))
               )
            .add(query.and
               .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_priority.columns.priorityid.eq('NORMAL'))
               )


I'm trying to change the where conditions to WHERE a AND b OR c and it brings the wrong results:

Code: Select all
query.where
            .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_department.columns.departmentid.eq('SUPPORT'))
            .add(query.or
               .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_statuscode.columns.statuscodeid.eq('COMMENCED'))
               .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_priority.columns.priorityid.eq('NORMAL'))
               )


I tried the code below, didn't work either. I can't seem to understand the mechanism behind. Please explain or send me to a doc?

Code: Select all
query.where
            .add(query.and
               .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_department.columns.departmentid.eq('SUPPORT'))
               .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_statuscode.columns.statuscodeid.eq('COMMENCED'))
               )
            .add(query.or
               .add(query.joins.job_to_jobstage.joins.jobstage_to_lookup_priority.columns.priorityid.eq('NORMAL'))
               )
Cheers,
Maria
maria.kyselova
 
Posts: 172
Joined: Thu Aug 09, 2012 12:56 am

Re: How do I search for nulls in related foundsets?

Postby maria.kyselova » Thu Apr 03, 2014 12:41 am

Ok, I figured it out. Thanks everyone!
Cheers,
Maria
maria.kyselova
 
Posts: 172
Joined: Thu Aug 09, 2012 12:56 am


Return to Methods

Who is online

Users browsing this forum: No registered users and 5 guests