Query Builder doesn't support subgrouping

QB in 1.8 "supported" nested grouping by producing wrong query. Ragnaroek take care of correct groups. This can be fixed with small QB API change and real nested groups support.

(In [18277]) Fix #339

I just tried out the new code and it doesn't seem to work correctly yet. When I run the following (from o.o.calendar/handler/agenda):

        // List user's event memberships
	$mc = midcom_db_eventmember::new_collector('uid', $_MIDGARD['user']);
	$mc->add_value_property('eid');

        // Find all events that occur during [$from, $end]
        $mc->begin_group("OR");
            // The event begins during [$from, $to]
            $mc->begin_group("AND");
                $mc->add_constraint("eid.start", ">=", $from);
                $mc->add_constraint("eid.start", "<=", $to);
            $mc->end_group();
            // The event begins before and ends after [$from, $to]
            $mc->begin_group("AND");
                $mc->add_constraint("eid.start", "<=", $from);
                $mc->add_constraint("eid.end", ">=", $to);
            $mc->end_group();
            // The event ends during [$from, $to]
            $mc->begin_group("AND");
                $mc->add_constraint("eid.end", ">=", $from);
                $mc->add_constraint("eid.end", "<=", $to);
            $mc->end_group();
        $mc->end_group();
	$mc->execute();

I get the following error on the web site:

( ! ) Warning: (pid:21107): QUERY FAILED: FUNCTION event.id does not exist QUERY: SELECT eventmember.guid AS midgard_collector_key, eventmember.eid AS eid FROM event, eventmember WHERE eventmember.uid = 3 AND ((event.event_end >= 1224540000 AND event.event_end <= 1224626399) AND eventmember.eid = event.id AND eventmember.eid = event.id(event.start <= 1224540000 AND event.event_end >= 1224626399) AND eventmember.eid = event.id AND eventmember.eid = event.id(event.start >= 1224540000 AND event.start <= 1224626399) AND eventmember.eid = event.id AND eventmember.eid = event.id) AND eventmember.sitegroup IN (0, 1) AND eventmember.metadata_deleted = 0 in /var/local/midgard-trunk/midcom/midcom.core/midcom/core/collector.php on line 219
Call Stack
#	Time	Memory	Function	Location
1	0.0081	126100	{main}( )	../61-305-68-0.php:0
2	1.6076	5222964	midcom_application->content( )	../61-305-68-0.php:182
3	1.6151	5281452	midcom_application->_output( )	../application.php:501
4	1.6240	5378180	midcom_baseclasses_components_interface->show_content( )	../application.php:1267
5	1.6240	5378180	midcom_baseclasses_components_request->show( )	../interface.php:590
6	1.6241	5378180	org_openpsa_mypage_handler_today->_show_today( )	../request.php:796
7	1.6249	5378252	midcom_show_style( )	../today.php:170
8	1.6250	5378252	midcom_helper__styleloader->show( )	../_styleloader.php:923
9	1.6340	5480656	eval( ''?>get_custom_context_data(\'request_data\');\n$prefix = $_MIDCOM->get_context_data(MIDCOM_CONTEXT_ANCHORPREFIX);\n$view_today =& $data[\'view_today\'];\n?>\n
\n \n
\n dynamic_load($data[\'calendar_url\'] . \'agenda/day/\' . date(\'Y-m-d\', $data[\'requested_time\']));\n ?>\n
\n )
	../_styleloader.php:618
10	1.6345	5481704	midcom_application->dynamic_load( )	../_styleloader.php(618) : eval()'d code:14
11	1.6357	5481836	midcom_application->_process( )	../application.php:650
12	1.7154	5722880	midcom_application->_handle( )	../application.php:989
13	1.7159	5722884	midcom_baseclasses_components_interface->handle( )	../application.php:1084
14	1.7159	5722884	midcom_baseclasses_components_request->handle( )	../interface.php:580
15	1.7364	5741968	org_openpsa_calendar_handler_agenda->_handler_day( )	../request.php:674
16	1.7486	5820568	midcom_core_collector->list_keys( )	../agenda.php:73
17	1.7487	5820568	midcom_core_collector->_list_keys_and_check_privileges( )	../collector.php:319
18	1.7487	5820568	midcom_core_collector->_real_execute( )	../collector.php:274
19	1.7487	5820568	midgard_collector->execute( )	../collector.php:219

The example code is using collector, but the error I get when I switch to QB is the same

I found another case where this happens: get_actions() in salesproject.php.

This code:

        $qb = org_openpsa_relatedto_relatedto_dba::new_query_builder();
        $qb->add_constraint('toGuid', '=', $this->guid);
        //In theory I could limit just by the class but this is more robust in the long run
        $qb->begin_group('OR');
            $qb->begin_group('AND');
                $qb->add_constraint('fromComponent', '=', 'org.openpsa.calendar');
                $qb->add_constraint('fromClass', '=', 'org_openpsa_calendar_event_dba');
            $qb->end_group();
            $qb->begin_group('AND');
                $qb->add_constraint('fromComponent', '=', 'org.openpsa.projects');
                $qb->add_constraint('fromClass', '=', 'org_openpsa_projects_task_dba');
            $qb->end_group();
        $qb->end_group();
        $links = $qb->execute();

produces this SQL query:

Dokumentation

SELECT org_openpsa_relatedto.guid, org_openpsa_relatedto.sitegroup, org_openpsa_relatedto.metadata_creator, NULLIF( org_openpsa_relatedto.metadata_created, '0000-00-00 00:00:00' ) AS metadata_created, org_openpsa_relatedto.metadata_revisor, NULLIF( org_openpsa_relatedto.metadata_revised, '0000-00-00 00:00:00' ) AS metadata_revised, org_openpsa_relatedto.metadata_revision, org_openpsa_relatedto.metadata_locker, NULLIF( org_openpsa_relatedto.metadata_locked, '0000-00-00 00:00:00' ) AS metadata_locked, org_openpsa_relatedto.metadata_approver, NULLIF( org_openpsa_relatedto.metadata_approved, '0000-00-00 00:00:00' ) AS metadata_approved, org_openpsa_relatedto.metadata_authors, org_openpsa_relatedto.metadata_owner, NULLIF( org_openpsa_relatedto.metadata_schedule_start, '0000-00-00 00:00:00' ) AS metadata_schedule_start, NULLIF( org_openpsa_relatedto.metadata_schedule_end, '0000-00-00 00:00:00' ) AS metadata_schedule_end, org_openpsa_relatedto.metadata_hidden, org_openpsa_relatedto.metadata_nav_noentry, org_openpsa_relatedto.metadata_size, org_openpsa_relatedto.metadata_published, NULLIF( org_openpsa_relatedto.metadata_exported, '0000-00-00 00:00:00' ) AS metadata_exported, NULLIF( org_openpsa_relatedto.metadata_imported, '0000-00-00 00:00:00' ) AS metadata_imported, org_openpsa_relatedto.metadata_deleted, org_openpsa_relatedto.metadata_score, org_openpsa_relatedto.metadata_islocked, org_openpsa_relatedto.metadata_isapproved, org_openpsa_relatedto.id AS id, org_openpsa_relatedto.id AS id, org_openpsa_relatedto.toClass AS toClass, org_openpsa_relatedto.status AS
STATUS , org_openpsa_relatedto.created AS created, org_openpsa_relatedto.revisor AS revisor, org_openpsa_relatedto.fromClass AS fromClass, org_openpsa_relatedto.toComponent AS toComponent, org_openpsa_relatedto.creator AS creator, org_openpsa_relatedto.revised AS revised, org_openpsa_relatedto.toExtra AS toExtra, org_openpsa_relatedto.fromGuid AS fromGuid, org_openpsa_relatedto.fromExtra AS fromExtra, org_openpsa_relatedto.revision AS revision, org_openpsa_relatedto.fromComponent AS fromComponent, org_openpsa_relatedto.toGuid AS toGuid
FROM org_openpsa_relatedto
WHERE org_openpsa_relatedto.toGuid = '39fcd8789fbd11dd8b23230e412f9f189f18'
AND (
(
org_openpsa_relatedto.fromComponent = 'org.openpsa.projects'
AND org_openpsa_relatedto.fromClass = 'org_openpsa_projects_task_dba'
)(
org_openpsa_relatedto.fromComponent = 'org.openpsa.calendar'
AND org_openpsa_relatedto.fromClass = 'org_openpsa_calendar_event_dba'
)
)
AND org_openpsa_relatedto.sitegroup
IN ( 0, 1 )
AND org_openpsa_relatedto.metadata_deleted =0
LIMIT 0 , 30 

It seems that there is simply an OR missing between the opening and closing brackets, hwne you add it, the query runs fine

(In [18402]) Set correct join constraints if only groups are used by QB. Fix SQL generation. Fix #339.