Doctrine2 with GROUP_CONCAT and non-related JOIN

Doctrine2 ORM is a fantastic and powerful object relational mapper (ORM) for PHP. We use it for IXP Manager to great effect and we only support MySQL so our hands are not tied to pure Doctrine2 DQL supported functions. We also use the excellent Laravel Doctrine project with the Berberlei extensions.

Sometimes time is against you as a developer and the documentation (and StackOverflow!) lacks the obvious solutions you need and you end up solving what could be a single elegant query very inefficiently in code with iterative database queries. Yuck.

I spent a bit of time last night trying to unravel one very bad example of this where the solution would require DQL that could:

group / concatenate multiple column results from a one-to-many relationship;
join a table without a relationship;
ensure the joining of the table without the relationship would not exclude results where the joint table had no matches;
provide a default value for (3).

Each of these was solved as follows:

via MySQL’s GROUP_CONCAT() aggregator. The specific example here is that when a MAC address associated with a virtual interface can be visible in multiple switch ports. We want to present the switch ports to the user and GROUP_CONCAT() allows us to aggregate these as a comma separated concatenated string (e.g. "Ethernet1,Ethernet8,Ethernet9").
Normally with Doctrine2, all relationships would be well-defined with foreign keys. This is not always practical and sometimes we need to join tables on the result of some equality test. We can do this using a DQL construct such as: JOIN Entities\OUI o WITH SUBSTRING( m.mac, 1, 6 ) = o.oui.
This is as simple as ensuring you LEFT JOIN.
The COALESCE() function is used for this: COALESCE( o.organisation, 'Unknown' ) AS organisation.

We have not yet pushed the updated code into IXP Manager mainline but the above referenced function / code is not replaced with the DQL query:

SELECT m.mac AS mac, vi.id AS viid, m.id AS id, 
    m.firstseen AS firstseen, m.lastseen AS lastseen,  
    c.id AS customerid, c.abbreviatedName AS customer,
    s.name AS switchname, 
    GROUP_CONCAT( sp.name ) AS switchport, 
    GROUP_CONCAT( DISTINCT ipv4.address ) AS ip4, 
    GROUP_CONCAT( DISTINCT ipv6.address ) AS ip6,
    COALESCE( o.organisation, 'Unknown' ) AS organisation

FROM Entities\\MACAddress m
    JOIN m.VirtualInterface vi
    JOIN vi.VlanInterfaces vli
    LEFT JOIN vli.IPv4Address ipv4
    LEFT JOIN vli.IPv6Address ipv6
    JOIN vi.Customer c
    LEFT JOIN vi.PhysicalInterfaces pi
    LEFT JOIN pi.SwitchPort sp
    LEFT JOIN sp.Switcher s
    LEFT JOIN Entities\\OUI o WITH SUBSTRING( m.mac, 1, 6 ) = o.oui

GROUP BY m.mac, vi.id, m.id, m.firstseen, m.lastseen, 
    c.id, c.abbreviatedName, s.name, o.organisation

ORDER BY c.abbreviatedName ASC

Share this: