Symfony 2 entity join or doctrine query join

Question

I was wondering if it was possible to configure a entity to automatically load data from another entity. Ex.

/**
 * accountsUsers
 *
 * @ORM\Table()
 * @ORM\Entity
 */
class accountsUsers
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    public $id;
    /**
     * @var string
     *
     * @ORM\Column(name="userid",type="integer")
     */
    public $userid;
    /**
     * @var string
     *
     * @ORM\Column(name="roleid",type="integer")
     */
    public $roleid;
    /**
     * @var string
     * admin,adviser,etc
     * @ORM\Column(name="roleType", type="string", length=255)
     */ 
    public $roleType;
    /**
     * @var string
     *
     * @ORM\Column(name="firstname", type="string", length=255)
     */
    public $firstname;
    /**
     * @var string
     *
     * @ORM\Column(name="lastname", type="string", length=255)
     */
    public $lastname;
    /**
     * @var string
     *
     * @ORM\Column(name="company", type="string", length=255)
     */
    public $company;
    /**
     * @var string
     *
     * @ORM\Column(name="url", type="string", length=255)
     */
    public $url;
    /**
     * @var string
     *
     * @ORM\Column(name="phone", type="string", length=255)
     */
    public $phone;
    /**
     * @var string
     *
     * @ORM\Column(name="phone2", type="string", length=255)
     */
    public $phone2;
    /**
     * @var string
     *
     * @ORM\Column(name="address", type="string", length=255)
     */
    public $address;
    /**
     * @var string
     *
     * @ORM\Column(name="address2", type="string", length=255)
     */
    public $address2;
    /**
     * @var string
     *
     * @ORM\Column(name="city", type="string", length=255)
     */
    public $city;
    /**
     * @var string
     *
     * @ORM\Column(name="state", type="string", length=255)
     */
    public $state;
    /**
     * @var string
     *
     * @ORM\Column(name="zip", type="string", length=255)
     */
    public $zip;
    /**
     * @var string
     *
     * @ORM\Column(name="email", type="string", length=255)
     */
    public $email;
    /**
     * @var string
     *
     * @ORM\Column(name="password", type="string", length=255)
     */
    public $password;
}

AND

/**
 * accounts
 *
 * @ORM\Table()
 * @ORM\Entity
 */
class accounts
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    public $id;
    /**
     * @var string
     *
     * @ORM\Column(name="partnerid", type="string", length=100)
     */
    public $partnerid;
    /**
     * @var \DateTime
     *
     * @ORM\Column(name="subscribedate", type="date")
     */
    public $subscribedate;
    /**
     * @var string
     *
     * @ORM\Column(name="connectionType", type="string", length=100)
     */
    public $connectionType;
    /**
     * @var string
     *
     * @ORM\Column(name="recordkeeperTpaid", type="string", length=100)
     */
    public $recordkeeperTpaid;
    /**
     * @var string
     *
     * @ORM\Column(name="recordkeeperAdviceProviderCd", type="string", length=100)
     */
    public $recordkeeperAdviceProviderCd;
    /**
     * @var string
     *
     * @ORM\Column(name="recordkeeperUrl", type="string", length=200)
     */
    public $recordkeeperUrl;
    /**
     * @var string
     *
     * @ORM\Column(name="recordkeeperUser", type="string", length=100)
     */
    public $recordkeeperUser;
    /**
     * @var string
     *
     * @ORM\Column(name="recordkeeperPass", type="string", length=100)
     */
    public $recordkeeperPass;
    /**
     * @var string
     *
     * @ORM\Column(name="recordkeeperPortfoliosAvailable", type="smallint")
     */
    public $recordkeeperPortfoliosAvailable;
    /**
     * @var string
     *
     * @ORM\Column(name="recordkeeperRiskBasedFundsAvailable", type="smallint")
     */
    public $recordkeeperRiskBasedFundsAvailable;
    /**
     * @var string
     *
     * @ORM\Column(name="investmentsMinScore", type="integer")
     */
    public $investmentsMinScore;
    /**
     * @var string
     *
     * @ORM\Column(name="investmentsMaxScore", type="integer")
     */
    public $investmentsMaxScore;
    /**
     * @var string
     *
     * @ORM\Column(name="ACAon", type="smallint")
     */
    public $ACAon;
    /**
     * @var string
     *
     * @ORM\Column(name="MSTClientID", type="string", length = 100)
     */
    public $MSTClientID;
}

Is it possible to set up accountsUsers to automatically load in all the accounts data when accountsUsers is accessed by findby, findbyone, etc. The relationship is accountsUsers.userid = accounts.id.

If not, how would I do this in a join using doctrine. I know how to do it using pure raw sql.

Ex. SELECT * FROM accounts a LEFT JOIN accountsUsers b ON a.id = b.userid

Answer 1

It is not possible to load separate entities in one query (i.e. with a JOIN) just by using annotations. You'll have to define a custom query in your repository which instructs Doctrine, via DQL or QueryBuilder, to join with the related entities.

It was a big gotcha for me as well, but using fetch="EAGER" in the annotation defining the association doesn't actually use a JOIN to retrieve the result - it just issues 2 separate queries to pre-fill the relationship so that its first access does not trigger the lazy loading process.

See also this question for more information.

Answer 2

Symfony developed relations which give you the ability to define a property in entity files and relate it to other entity. There are two ways.

First way:

In your case, first you have to make a column in users table(for example name it account) and relate it to account.id column using foreign key's, then in App\Entity\Accounts you can define a property name $users and relate it to App\Entity\Users like code below:

   /**
     * @ORM\OneToMany(targetEntity="App\Entity\Users", mappedBy="account")
     */
    private $users;

and one in App\Entity\Users

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Accounts", inversedBy="users")
     */
    private $Account;

like this whenever you get a object from App\Entity\Accounts a key with the name users will return that have the information of all of this account users just the result:

    SELECT * FROM accounts a LEFT JOIN accountsUsers b ON a.id = b.userid

will give you.

The second way is pretty easy just by using make:entity command

php bin/console make:entity

you can make a new column and make a relation for it, command will ask you about everything step by step and its pretty much so simple working with it that you don't need to read the document but if you need more information this is the link to read about it https://symfony.com/doc/current/doctrine/associations.html#the-manytoone-onetomany-association