inverted find needed

Classic Servoy
1

Hi everyone,

I’m stuck here (it’s too late anyway) so maybe someone can help me out here.
We’ve a scripted search routine in order to test searchcriteria before accepting them as such.

Consider the following situation:
table a holding records which is related to table b holding records but NOT for every record in table a.

now I want to do a search for records in table a containing certain criteria in field xx and that doen’t have any related record in table b.
So to explain a bit more, this example: I want to search for all orders with a certain date which don’t have any related orderdetail records…

In FileMaker this was quite easy using the omit function on a searchrequest, in Servoy this seems harder.
Anyone having experience on this situation?

Any help is much appreciated!

Thnkx

2

Hi Marc,

So you want all orders that don’t have any order line-items but only from a certain date.
I think you need to do this with plain SQL.

var _sQuery = "SELECT o.orderid FROM orders o LEFT JOIN orderdetails od ON o.orderid=od.orderid WHERE od.orderid IS NULL and o.orderdate=?";
controller.loadRecords(_sQuery, [myDateValue]);

Hope this helps.

3

You can search on the related PK('s) being null (using normal find/search syntax).

Paul

4

I guess you’re right.
Of course you do need to use a Servoy relationship that is set to use an outer join, not the default inner join.

5

Thnkx Robert & Paul,

as we really do a scripted search (basically the default Servoy search with some more intelligence wrapped around it) SQL is not really an option.

Paul is right (and he should ;-) ) about the related ‘null’ search, however it seems that it doesn’t really have to be the PK.

Unfortunately it doesn’t work for the relation I’m testing right now. It’s a relationship on pk = parent pk and a global integer = record_type

6

Hi Marc,

A PK can never be NULL so it’s the safest way to check for a non-related record. Other columns might have NULLs.
As for your relation. The global integer = record_type is an extra filter (WHERE clause) that makes it impossible to get the records you want so I suggest you create another (outer join) relation that doesn’t have this global defined in it.

Hope this helps.

7

Hi Robert,

I found the problem… it’s not the relation, but the type of element that was bugging me.
the element is of type html and during the find that’s converted into something different than the plain ‘^’ character I need.

So actually the multi criteria relation does work fine.
As for using the pk or not, I realize the pk is definitely the best column to use, but unfortunately never used as a (plain) dataProvider in the UI.
Maybe I can do some conversion on that one when the user does a related search with criteria ‘^’…

Thnkx again for your input

8

Hmm…that relation works for you ? I guess only when you put a NULL in the global, right?
If that recordtype column is always filled in then putting a NULL in the global is enough to get your result (apart from searching on the date).

9

ROCLASI:
I guess only when you put a NULL in the global, right?

No that’s not the case. This global is a constant value of 2.

10

Hi Marc,

That’s just weird that it works for you then.
As far as I know the SQL that should come out of that is the following:

SELECT o.pk FROM orders o LEFT JOIN orderdetails od ON o.pk=od.parentpk WHERE od.recordtype=2

This would give all the RELATED records with recordtype 2
Now when you search for the NON-RELATED records like so:

SELECT o.pk FROM orders o LEFT JOIN orderdetails od ON o.pk=od.parentpk WHERE od.recordtype=2 AND od.parentpk IS NULL

You should getting zero records back. Since you search for order records with no orderdetail records you won’t find any recordtype=2 records either, but you filter on it so it will never match any records.
So if you are getting records back then I really wonder what Servoy is doing. It doesn’t make any sense.

11

mboegem:
So if you are getting records back then I really wonder what Servoy is doing.

it’s even a more complex query as it also takes the tablefilters for both tables into account…
but… you don’t hear me complain anymore :-)

12

Adding more (table) filters should never give you more records,only less. But if you are happy… :)

13

Marc,

Have you also considered searching on aggregates?

if (foundset.find())
{
   orders_to_order_details.detail_count=0
   foundset.search()
}

Rob

14

rgansevles:
Have you also considered searching on aggregates?

Nope… to be honest: the idea never even crossed my mind.

This could be a good working solution. Only thing is: we hardly use aggregates so I really should start creating them in every table in order to use them in globals methods.

Thnkx for the input!