doctrine-dbal

Doctrine DBAL clear from array of QueryBuilder


Updated problem

I'm using Doctrine DBAL. Everything worked fine until I use the dependency injection for the QueryBuilder twice in one api request.

The "from" part saves the first table I used and adds the new table/view for a different call to the from array, therfore it uses the t_members and the v_members in combination.

Here is my code I'm talking about.

private QueryBuilder $query;
private string $tablename = 't_members';
private string $viewname = 'v_members';
private string $tablealias = 'm';

public function __construct(private Connection $connection) {
    $this->query = $this->connection->createQueryBuilder();
}

//get all members of organization
function getAllByOrganization(): array {
    // TODO change to variable organization
    $qry = $this->query
    ->select('*')#'id', 'nickname')
    ->from($this->viewname)
    ->where('org_id = :organization')
    ->setParameter('organization', AuthMember::getOrganization())
    ->getSQL();
    die($qry);
}

function getAuthMemberData(int $id): array{
    return $this->query
        ->select(
            'id',
            'active',
            'test',
            'organization_id',
            'group_id'
            )
        ->from($this->tablename)
        ->where('id = :id')
        ->setParameter('id', $id)
        ->executeQuery()
        ->fetchAssociative() ?? [];
}

I'm calling the getAuthMemberData with the t_members. The next call, is v_members.

The SQL Statement of the function looks like this: SELECT * FROM t_members, v_members WHERE org_id = :organization

How can I reset the from part of the QueryBuilder to use the right rootes i want?


Solution

  • Simplest and suggested solution is to NOT create the query in the constructor but within each method, e.g.

    
    //get all members of organization
    function getAllByOrganization(): array {
        // TODO change to variable organization
        $qry = $this->connection->createQueryBuilder()
        ->select('*')#'id', 'nickname')
        ->from($this->viewname)
        ->where('org_id = :organization')
        ->setParameter('organization', AuthMember::getOrganization())
        ->getSQL();
        die($qry);
    }
    
    function getAuthMemberData(int $id): array{
        return $this->connection->createQueryBuilder()
            ->select(
                'id',
                'active',
                'test',
                'organization_id',
                'group_id'
                )
            ->from($this->tablename)
            ->where('id = :id')
            ->setParameter('id', $id)
            ->executeQuery()
            ->fetchAssociative() ?? [];
    }