Doctrine DBAL clear from array of QueryBuilder

Question

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?

Answer 1

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() ?? [];
}