Selecting from joined table

[bandtank]

I wrote a join query that works as expected, but I can't figure out how to select from the joined table.

$where = array("type" => "Post", "user.preferredName" => "...");
$select = array("user.id","id");

$notes = $this->entityManager->getRDBRepository("Note")
  ->leftJoin("user", "user", ["user.id:" => "createdById"])
  ->select($select)->where($where)->find();

foreach($notes as $note)
  $GLOBALS["log"]->debug("post",[$note->toArray()]);​ 

Produces:

[2022-09-28 12:10:11] DEBUG: ["SELECT user.id AS `user.id`, note.id AS `id` FROM `note` ...​
[2022-09-28 12:08:33] DEBUG: post [{"id":"6331e96d0d639be6d"}] []

I was expecting to see the user's ID as well.

[yuri]

Notes (any) entity can't receive foreign fields, only its defined fields. So using the Repository is not an option. Use the Query Builder:

$query = $this->entityManager
    ->getQueryBuilder()
    ->select([
        'id',
        ['createdBy.id', 'createdById'], // expression and alias
    ])
    ->from('Note')
    ->leftJoin('createdBy') // no need conditions if 'createdBy' is a link name
    //->leftJoin(
    //    'User', // User is an entity name (not a link name), first letter should be upper case
    //    'createdBy',
    //    ['createdBy.id:' => 'createdById']
    //)
    ->build();

// PDOStatement
$sth = $this->entityManager->getQueryExecutor()->execute($query);

$list = [];

while ($row = $sth->fetch()) {
    $list[] = $row;
} 

BTW If you use IDE it should hint method usage. You can use pure OOP to specify select expressions.

[bandtank]

Thanks. I get it now. That makes a lot of sense.