find mode with empty connection

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

find mode with empty connection

Postby nick1461658753 » Fri May 05, 2017 4:04 pm

Hi,

I'm trying to do a search on a foundset. But I'd like to have all the results with NO connections but can't seem to make it work

Tried a few things:
Code: Select all
if ( FS.find( ) ) {
      ->FS.location_to_box.getSize() == 0;
      ->FS.location_to_box.id = '!%';
      ->FS.location_to_box.id = '^';
      ->FS.location_to_box.id = '^=';
      FS.search( );
   }




Is there a way to do this? or am I gonna have to loop and check them all one by one if the getSize gives back 0? because most of these give either 0 results, or all results...
How about pizza?
nick1461658753
 
Posts: 23
Joined: Tue Apr 26, 2016 10:19 am

Re: find mode with empty connection

Postby lwjwillemsen » Fri May 05, 2017 5:23 pm

To be able to test for existence over relation in find / search would be great.
I miss it every day!
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 561
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: find mode with empty connection

Postby ROCLASI » Sun May 07, 2017 2:42 pm

You mean doing something like this?

Code: Select all
foundset.loadRecords("SELECT pkLocation FROM location LEFT JOIN box ON (fkBox=pkBox) WHERE pkBox IS NULL");


Hope this helps.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5201
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: find mode with empty connection

Postby lwjwillemsen » Sun May 07, 2017 3:17 pm

Yes, and that without sql select coding in 100% Servoy find / search functionality.
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 561
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: find mode with empty connection

Postby ROCLASI » Sun May 07, 2017 10:47 pm

Hi Lambert,

lwjwillemsen wrote:Yes, and that without sql select coding in 100% Servoy find / search functionality.


I am pretty sure this is not possible other then using QueryBuilder or SQL.
I suggest you file a feature request in Jira.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5201
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: find mode with empty connection

Postby nick1461658753 » Sun May 07, 2017 11:57 pm

So simply put, I'll just have to do it with a sql query?
Well that will have to do then :p

thx for the replies
How about pizza?
nick1461658753
 
Posts: 23
Joined: Tue Apr 26, 2016 10:19 am

Re: find mode with empty connection

Postby Bernd.N » Mon May 08, 2017 8:09 am

And as that is 100% SQL standard, there won't be any issues with SQL compatibility.
We use SQL a lot to create foundsets, as when it gets complex it is easier to overview the code.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 472
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: find mode with empty connection

Postby sean » Wed May 10, 2017 4:17 pm

This cannot be done in find mode which only supports joins and where clauses.

We recommend to use the QueryBuilder API over hand-written SQL.
It supports the sql EXISTS clause
This can be used in the WHERE clause to check for the existence of records in a subquery.

It also supports sql HAVING clause
This can be used to put aggregates in the WHERE clause, such as "I want records with no child records"
Software Engineer
Servoy USA
sean
 
Posts: 85
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: find mode with empty connection

Postby lwjwillemsen » Wed May 10, 2017 4:55 pm

This cannot be done in find mode which only supports joins and where clauses.


Why not? There are already so many special effects in find mode to influence the data retrieval! (See the wiki)
Imho it should be technically possible to add another trick / feature...
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 561
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: find mode with empty connection

Postby patrick » Wed May 10, 2017 6:05 pm

any suggestion how that could look like?
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3592
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: find mode with empty connection

Postby lwjwillemsen » Thu May 11, 2017 4:34 pm

Good question Patrick! Let's give it a try:

a.find()
a.relation_a_to_b.$count = ">0"
a.search()

$count is the new feature representing the number of rows present in a relation.

Powerful and short hand. I guess the number of rows can influence the performance (query speed).

Thanks for asking!
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 561
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: find mode with empty connection

Postby patrick » Thu May 11, 2017 5:45 pm

Then probably more straight forward would be

Code: Select all
a.find()
a.relation_a_to_b.getSize() = ">0"
a.search()


This would have to work in all cases of find()/search() then.

For example:

Code: Select all
a.find()
a.x = 'Foo';
a.relation_a_to_b.getSize() = '>3';
a.relation_a_to_b.y = 'Bar';
a.newRecord();
...


Maybe you can twist your head and figure out if that could easily work in all occasions :)
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3592
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: find mode with empty connection

Postby lwjwillemsen » Fri May 12, 2017 11:31 pm

Yes, getSize() would certainly be nicer!
I think it is doable but I don't know if the transformation to SQL is done by Servoy code or by another framework.
I know in SQL you can alias tables so a certain table can be queried of counted many times in one (big) SQL query.

I personally would love this new feature since I use find / search a lot because you can use the Servoy relations amongst many other nice features
and Servoy takes care for the SQL. I use SQL directly for more complexe queries which are harder to do in find / search.
With this new feature the data retrieval possibilities "range" for find/search becomes wider.

For existence check you can now use:
a.relation_a_to_b.b_pk_field = '>0' if you use integer pk fields.

For non existence check i see no solution:
a.relation_a_to_b.b_pk_field = '^' does not do the job. Even when relation_a_to_b = left outer join.
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 561
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: find mode with empty connection

Postby patrick » Sat May 13, 2017 10:09 am

So maybe you file a feature request and R&D can see if 1. it is doable and 2. it would be a worthwhile new feature from their point of view.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3592
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 2 guests