Relation not giving expected results

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Relation not giving expected results

Postby Richard1521662995 » Fri Feb 19, 2021 6:19 pm

I have created a relation
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 ^||= table2.field3
table1.field4 ^||= table2.field4

if for example table1.field4 and table2.field4 are both null then the relation doesn't find any data.

However, with the same data

if ( table1.find()) {
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 = '^||=' + table2.field3
table1.field4 = ' ^||=' + table2.field4

returns the expected data.

Can anyone help with why the relation doesn't return any data.

TIA
Richard Clarke
acss.co.uk
Richard1521662995
 
Posts: 39
Joined: Wed Mar 21, 2018 10:09 pm

Re: Relation not giving expected results

Postby Joas » Mon Feb 22, 2021 3:51 pm

Hi Richard,

What query do you get when you do:
Code: Select all
databaseManager.getSQL(relatedFoundSet)
Joas de Haan
Yield Software Development
Need help on your project? yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 842
Joined: Mon Mar 20, 2006 4:07 pm
Location: Leusden, NL

Re: Relation not giving expected results

Postby Richard1521662995 » Tue Feb 23, 2021 10:25 am

Hi Joas,

It returns

select
Table2.Table2_id
from
Table2
where
(Table2.Field1 = ?
and Table2.Field2 = ?
and (Table2.Field3 = ?
or Table2.Field3 is null)
and (Table2.Field4 = ?
or Table2.Field4 is null))
order by
Table2.Table2_id asc

for Table2.find()
select
Table2.Table2_id
from
Table2
where
(Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and (Table2.Field4 is null
or Table2.Field4 = ?)
and Table2.Field1 = ?
order by
Table2.Table2_id asc

Table2.find() is coded in field order


Thanks
Richard Clarke
acss.co.uk
Richard1521662995
 
Posts: 39
Joined: Wed Mar 21, 2018 10:09 pm

Re: Relation not giving expected results

Postby Joas » Tue Feb 23, 2021 11:39 am

That looks OK to me too.
If you run that query yourself, do you get the right data?

And if you perform the find()-search() and afterwards do databaseManager.getSQL() on that foundset, what query do you get?
Joas de Haan
Yield Software Development
Need help on your project? yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 842
Joined: Mon Mar 20, 2006 4:07 pm
Location: Leusden, NL

Re: Relation not giving expected results

Postby Richard1521662995 » Tue Feb 23, 2021 12:04 pm

After
if ( table1.find()) {
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 = '^||=' + table2.field3
table1.field4 = ' ^||=' + table2.field4

select
Table2.Table2_id
from
Table2
where
(Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and Table2.Field4 is null
and Table2.Field1 = ?
and (Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and Table2.Field4 is null
and Table2.Field1 = ?
order by
Table2.Table2_id asc

after the search is the one in the previous post.
Richard Clarke
acss.co.uk
Richard1521662995
 
Posts: 39
Joined: Wed Mar 21, 2018 10:09 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 13 guests