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
