Issue with Doctrine ORM Query - Finding User Contacts in Both Directions

Question

I'm encountering a challenging issue with a Doctrine ORM query related to finding user contacts based on my Account and Contact entities. Let me provide some context:

Entity Structure:

Contact Entity:

/**
 * @ORM\ManyToOne(targetEntity=Account::class, inversedBy="contacts")
 * @ORM\JoinColumn(nullable=false)
 */
private $Account_source;

/**
 * @ORM\ManyToOne(targetEntity=Account::class, inversedBy="contacts_backward")
 * @ORM\JoinColumn(nullable=false)
 */
private $Account_Targets;

Account Entity:

/**
 * @ORM\OneToMany(targetEntity=Contact::class, mappedBy="Account_source", orphanRemoval=true)
 */
private $contacts;

/**
 * @ORM\OneToMany(targetEntity=Contact::class, mappedBy="Account_Targets", orphanRemoval=true)
 */
private $contacts_backward;

Issue Description:

I'm trying to write a Doctrine ORM query that allows me to find user contacts for a given user ($searcherId), where that user can be either the Account_source or the Account_Targets in the contact relationship. The goal is to find contacts in both directions of the relationship.

The Initial Query:

Initially, I wrote the following query, which only found contacts in one direction of the relationship, where the logged in user was the Account_Target:

public function searchByName($value, $searcherId)
{
    return $this->createQueryBuilder('a')
        ->select('a.firstName as firstname', 'a.lastName as lastname', 'a.username as username', 'a.id as id')
        ->leftJoin('a.contacts', 'c')
        ->where('c.Account_source = :id AND a.id = c.Account_Targets')
        ->andWhere('(a.username like :val OR a.firstName like :val OR a.lastName like :val)')
        ->setParameter('val', '%' . $value . '%')
        ->setParameter('id', $searcherId)
        ->orderBy('a.firstName', 'ASC')
        ->getQuery()
        ->getResult();
}

The Problem:

The initial query worked for finding contacts in one direction where the user logged in was the Account_Target. However, it didn't cover the scenario where the user logged in could also be the Account_source.

For example, Ostwind(targets) can find Skorpiono(source) but Skorpiono(source) also can only find Skorpiono(source)

Attempted Solution:

To address this limitation, I modified the query to include both directions of the relationship, as follows:

// The modified query that attempted to find contacts in both directions
public function searchByName($value, $searcherId)
{
    return $this->createQueryBuilder('a')
        ->select('a.firstName as firstname', 'a.lastName as lastname', 'a.username as username', 'a.id as id')
        ->leftJoin('a.contacts', 'c')
        ->leftJoin('a.contacts_backward', 'cb')
        ->where('(c.Account_source = :id AND a.id = c.Account_Targets) OR (cb.Account_Targets = :id AND a.id = cb.Account_source)')
        ->andWhere('(a.username like :val OR a.firstName like :val OR a.lastName like :val)')
        ->setParameter('val', '%' . $value . '%')
        ->setParameter('id', $searcherId)
        ->orderBy('a.firstName', 'ASC')
        ->getQuery()
        ->getResult();
}

The Current Issue:

Now, the current issue is that the modified query doesn't return any results, and I'm unable to find contacts in either direction of the relationship.

Answer 1

I found answer, it was UNION

SELECT 
                a0_.first_name AS first_name, 
                a0_.last_name AS last_name, 
                a0_.username AS username,
                a0_.id AS id
            FROM 
                contact c2_ 
                LEFT JOIN account a0_ ON c2_.account_source_id = a0_.id 
            WHERE 
                c2_.account_targets_id = :id 
                AND (a0_.first_name LIKE :val OR a0_.last_name LIKE :val OR a0_.username LIKE :val)

            UNION

            SELECT 
                a1_.first_name AS first_name, 
                a1_.last_name AS last_name, 
                a1_.username AS username,
                a1_.id AS id
            FROM 
                contact c2_ 
                LEFT JOIN account a1_ ON c2_.account_targets_id = a1_.id 
            WHERE 
                c2_.account_source_id = :id 
                AND (a1_.first_name LIKE :val OR a1_.last_name LIKE :val OR a1_.username LIKE :val)