Extending foundset find with querybuilder

Classic Servoy
1

I’ve got a table calls, this is related to the table tasks using the relation calls_to_tasks$open. This will give me all open tasks for a given call.
Using this relation I want to search in calls to get all records that have no open tasks at this time. I don’t think I can do this in my regular search, so i’m experementing for the first time with the querybuilder to extend my search, but I’m not sure if this will work.
I already have an advanced search form where the user can enter multiple searchfields. This is used to build a find on my foundset and I want to extend this to include the new search option.

This is what I’ve got sofar:

			/**@type {QBSelect<db:/welcome/calls>}*/
			var qb = _fs.getQuery();
			/**@type {QBJoin<db:/welcome/tasks>}*/
			var taskJoin = qb.joins.add('db:/welcome/tasks', JSRelation.INNER_JOIN);
			taskJoin.on.add(qb.and.add(qb.columns.call_id.eq(taskJoin.columns.task_callid)).add(taskJoin.columns.datfin.isNull))
			qb.where.add(taskJoin.columns.task_id.isNull);
			_fs.loadRecords(qb)

However this is not resulting in any filtering.

This is the sql statement that I want to build in the querybuilder:

			select * from calls c
			left join tasks t on c.call_id=t.task_callid and t.datfin is null
			where t.task_id is null
2

Hi Jos,

I personally would move the ‘and t.datfin is null’ from the join to the where clause.

So : select * from calls c
left join tasks t on c.call_id=t.task_callid
where t.task_id is null and t.datfin is null

You already use the join ‘t’ in the where clause.
I think translated to the Servoy query object it will work then…

Groeten/Regards,