Doctrine 3 native query

Question

I'm trying to make a native query with Doctrine 3 (in a Symfony 6 context). My problem took place when I switched from MySQL to PostgreSQL. My code work with MySQL and the native query itself is right.

This query is a bit complex in sense that I'm using Union, non-mapped/entity based result and also a concat on embedded entity fields (even if this shouldnt be a problem in a native query)

I've got a bunch of entity joined:

Customer -> CustomerSite -> CustomerSiteContact

Prospect -> ProspectSite -> ProspectSiteContact

Supplier -> SupplierContact

I'll only show you how is my Customer and its joined entity because the logic is the same for the other entity.

My model

#[ORM\Entity]
class Customer extends Aggregate
{
    use TimeStampableTrait;
    use SoftDeletableTrait;

    #[ORM\Id, ORM\Column(type: 'integer', options: ['unsigned' => true]), ORM\GeneratedValue(strategy: 'IDENTITY')]
    protected int $id;

    #[ORM\Column(type: 'string', length: 50)]
    protected string $customerCode;

    #[ORM\ManyToOne(targetEntity: Agency::class, inversedBy: 'customers')]
    protected Agency $agency;

    #[ORM\Column(type: 'string', length: 255)]
    protected string $name;

    #[ORM\Embedded(class: EmbeddableAddress::class, columnPrefix: false)]
    protected EmbeddableAddress $address;

    #[ORM\OneToMany(mappedBy: 'customer', targetEntity: CustomerSite::class, cascade: ['persist'])]
    protected Collection $sites;
//...
}

#[ORM\Entity]
class CustomerSite extends Aggregate
{
    #[ORM\Id, ORM\Column(type: 'integer', options: ['unsigned' => true]), ORM\GeneratedValue(strategy: 'IDENTITY')]
    protected int $id;

    #[ORM\Column(type: 'uuid', unique: true)]
    protected UuidInterface $uuid;

    #[ORM\Column(type: 'string', length: 255)]
    protected string $name;

    #[
        ORM\ManyToOne(targetEntity: Customer::class, inversedBy:'sites'),
        ORM\JoinColumn(nullable: false)
    ]
    protected Customer $customer;

    #[ORM\Column(type: 'string', length: 50)]
    protected string $phone;

    #[ORM\Column(type: 'string', length: 180)]
    protected string $email;

    #[ORM\Embedded(class: EmbeddableAddress::class, columnPrefix: false)]
    protected EmbeddableAddress $address;

    #[ORM\OneToMany(mappedBy: 'site', targetEntity: CustomerSiteContact::class, cascade: ['persist', 'remove'])]
    protected Collection $contacts;
//...
}

#[ORM\Entity]
class CustomerSiteContact extends Aggregate
{
    use TimeStampableTrait;
    use SoftDeletableTrait;

    #[ORM\Id, ORM\Column(type: 'integer', options: ['unsigned' => true]), ORM\GeneratedValue(strategy: 'IDENTITY')]
    protected int $id;

    #[ORM\Column(type: 'uuid', unique: true)]
    protected UuidInterface $uuid;

    #[ORM\ManyToOne(targetEntity: CustomerSite::class, inversedBy: 'contacts')]
    #[ORM\JoinColumn(nullable: false)]
    protected CustomerSite $site;

    #[ORM\Embedded(class: EmbeddablePersonInformation::class, columnPrefix: false)]
    protected EmbeddablePersonInformation $contactInformation;

    #[ORM\Column(type: 'string', length: 50)]
    protected string $functionInCompany;
//...
}

And the embeddable address

#[ORM\Embeddable]
class EmbeddableAddress
{
    #[ORM\Column(type: 'string', length: 200, nullable: true)]
    protected ?string $street;

    #[ORM\Column(type: 'string', length: 10, nullable: true)]
    protected ?string $zipcode;

    #[ORM\Column(type: 'string', length: 50, nullable: true)]
    protected ?string $city;

    #[ORM\Column(type: 'string', length: 56, nullable: true)]
    protected ?string $country;
//...
}

And finally the embeddable Address

#[ORM\Embeddable]
class EmbeddablePersonInformation
{
    #[ORM\Column(type: 'string', length: 50)]
    protected string $firstname;

    #[ORM\Column(type: 'string', length: 50)]
    protected string $lastname;

    #[ORM\Column(type: 'string', length: 101)]
    protected string $displayName;

    #[ORM\Column(type: 'string', length: 50)]
    protected string $phone1;

    #[ORM\Column(type: 'string', length: 50, nullable: true)]
    protected ?string $phone2;

    #[ORM\Column(type: 'string', length: 180)]
    protected string $email;
//...
}

Here is what I'm trying

public function findRequestersByAgencies(Collection $agencies, Client $client): array
    {
        $rsm = new ResultSetMapping();
        $rsm
            ->addScalarResult(columnName: 'contactName', alias: 'contactName')
            ->addScalarResult(columnName: 'contactUuid', alias: 'contactUuid')
            ->addScalarResult(columnName: 'contactPhone', alias: 'contactPhone')
            ->addScalarResult(columnName: 'contactEmail', alias: 'contactEmail')
            ->addScalarResult(columnName: 'contactJobTitle', alias: 'contactJobTitle')
            ->addScalarResult(columnName: 'requesterDenomination', alias: 'requesterDenomination')
            ->addScalarResult(columnName: 'requesterCode', alias: 'requesterCode')
            ->addScalarResult(columnName: 'siteName', alias: 'siteName')
            ->addScalarResult(columnName: 'siteUuid', alias: 'siteUuid');

        return $this->em->createNativeQuery(
            <<<SQL
SELECT csc.display_name as contactName, csc.uuid as contactUuid, csc.phone1 as contactPhone, csc.email as contactEmail, csc.function_in_company as contactJobTitle,
       c.name as requesterDenomination, c.customer_code as requesterCode, 'customer' as requesterType,
       cs.name as siteName, cs.uuid as siteUuid, CONCAT(cs.street, ' - ', cs.zipcode, ' ', cs.city) as siteAddress
FROM customer c
LEFT JOIN customer_site cs ON c.id = cs.customer_id
LEFT JOIN customer_site_contact csc ON cs.customer_id = c.id
WHERE c.agency_id IN (:customer_agencies_uuid)
UNION ALL
SELECT sc.display_name as contactName, sc.uuid as contactUuid, sc.phone1 as contactPhone, sc.email as contactEmail, sc.function_in_company as contactJobTitle,
       s.name as requesterDenomination, s.supplier_code as requesterCode, 'supplier' as requesterType,
       null as siteName, null as siteUuid, null as siteAddress
FROM supplier s
LEFT JOIN supplier_contact sc ON sc.supplier_id = s.id
WHERE s.client_id = :client
UNION ALL
SELECT psc.display_name as contactName, psc.uuid as contactUuid, psc.phone1 as contactPhone, psc.email as contactEmail, psc.function_in_company as contactJobTitle,
       p.name as requesterDenomination, p.prospect_code as requesterCode, 'prospect' as requesterType,
       ps.name as siteName, ps.uuid as siteUuid, CONCAT(ps.street, ' - ', ps.zipcode, ' ', ps.city) as siteAddress
FROM prospect p
LEFT JOIN prospect_site ps on p.id = ps.prospect_id
LEFT JOIN prospect_site_contact psc on ps.prospect_id = p.id
WHERE p.agency_id IN (:prospect_agencies_uuid)
SQL,
            $rsm
        )->setParameter('customer_agencies_uuid', $agencies)
            ->setParameter('prospect_agencies_uuid', $agencies)
            ->setParameter('client', $client)
            ->getArrayResult();
    }

I dont get any error, just an empty array as result. The request is fine as i can use it with

php bin/console doctrine:query:sql "the query..."

If anyone could help me, it would be awesome !

Thanks by advance