How to get an id without join in doctrine2?

Question

I have entity like this:

/**
 *
 * @Table(name="table")
 * @Entity
 */
 class Table {

    /**
     * @Column(type="integer")
     * @Id
     * @GeneratedValue(strategy="IDENTITY")
     */
     private $id;


    /**
     * @ManyToOne(targetEntity="Entities\Users")
     * @joinColumn(name="userId", referencedColumnName="id")
     */
     private $User;


    /**
     * @Column(type="string")
     */
     private $text;


}

If i do $q->getQuery()->getSingleResult()->getUser()->getUserId()

doctrine generate query like:

SELECT * FROM table t INNER JOIN users u ON u.id = t.userId WHERE id = 100

but if i don`t need table users, how to get an userId.

In pure SQL i can just

SELECT * FROM table WHERE id = 100

and get userId without join users table.

Lupulin

Answer 1

You may also want to look at the IDENTITY() function (Doctrine version >2.2).

Example:

SELECT IDENTITY(t.User) AS user_id from Table

Should return:

[ ['user_id' => 1], ['user_id' => 2], ... ]

See also: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#dql-functions

Brander

Answer 2

Try this:

$q = $qb->getQuery();
$q->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true);

Ditchwater

Answer 3

According to the documentation, D2 entities should never need the foreign key exposed as it is all done internally by the mapping information, however, as you have found out, there is a need now and then to have this value. To do this, simply supply the mapping info for the foreign key.

Fears

Answer 4

I don't know doctrine2 but from what I read this is a ORM. So I would suggest that you need a form of lazy loading where user isn't loaded. This article suggests that lazy loading is available in doctrine2.

This may result in multiple db calls if user is later used on the returned table. You have to weigh this up with the idea of grabbing the data in one hit.

Unmerciful

do not answer questions, if you are not familiar with the subject! – Mureil

@Mureil I answered this in October 2010 there were no answers after mine until May 2011. That's 7 months after the question the posted. Though in general you could be right from one limited perspective, the user would have not got a timely answer for their problem. One could also argue that since I was very familiar with ORMs I felt my answer was appropriate at the time. – Unmerciful