55190

ZF2 - Doctrine ORM, Simple Table Join

Question:

I am currently learning how to use Doctrine ORM with ZF2 and currently my goal is to retrieve data from a simple table join and display it to screen.

I have read the documents and it looks pretty simple to do.

These are my tables:

user ------------------------ |user_id | name | email | -------------------------- | 1 | John | j@b.com | -------------------------- | 2 | Bob | b@j.com | -------------------------- user_role_linker -------------------------- |user_id | role_id | -------------------------- | 1 | administrator | -------------------------- | 2 | staff | --------------------------

What I want to achieve is a list to my view as follows:

ID Name Email Role Actions -------------------------------------------------------- 1 John j@b.com Administrator Edit 2 Bob b@j.com Staff Edit -------------------------------------------------------- Paging goes here ----------------

This is what I currently have and it seems to work except I am not sure how to grab the joined table data:

User entity:: <?php namespace Administration\Entity; use Doctrine\ORM\Mapping as ORM; use Doctrine\ORM\Mapping\ManyToMany; use Doctrine\ORM\Mapping\JoinTable; use Doctrine\ORM\Mapping\JoinColumn; use Doctrine\Common\Collections\ArrayCollection; /** @ORM\Entity */ class User { /** * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") * @ORM\Column(type="integer",name="user_id") */ protected $user_id; /** @ORM\Column(type="integer", name="parent_id") */ protected $parent_id; /** @ORM\Column(type="string", name="name") */ protected $name; /** @ORM\Column(type="string", name="email") */ protected $email; //Setters and getters public function getUserId() { return $this->user_id; } public function setName($name) { $this->name = $name; } public function getName() { return $this->name; } public function getEmail() { return $this->email; } public function setEmail($email) { $this->email = $email; } /** * @ManyToMany(targetEntity="UserRoleLinker") * @JoinTable( * name="user_role_linker", * joinColumns={ * @JoinColumn( * name="user_id", * referencedColumnName="id") * }, * inverseJoinColumns={ * @JoinColumn( * name="user_id", * referencedColumnName="id", * unique=true) * }) */ private $role_id; public function __construct() { $this->role_id = new ArrayCollection(); } /** @return Collection */ public function getRoleId() { return $this->role_id; } }

User role linker entity::

<?php namespace Administration\Entity; use Doctrine\ORM\Mapping as ORM; /** @ORM\Entity */ class UserRoleLinker { /** * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") * @ORM\Column(type="integer",name="user_id") */ protected $user_id; /** @ORM\Column(type="string", name="role_id") */ protected $role_id; /** @param User|null */ public function getRoleId() { return $this->role_id; } }

My Administration controller::

public function usersAction() { $em = $this->getServiceLocator()->get('Doctrine\ORM\EntityManager'); $userFunctions = new UserFunction($em); $userArray = $userFunctions->getUsers(); $viewModel = new ViewModel(array('users' => $userArray)); return $viewModel; }

This calls my UserFunctions class::

public function getUsers() { //This function returns the users return $this->em->getRepository('Administration\Entity\User')->findAll(); }

And in my view I list the data like this:

<?php foreach ($this->users AS $user) { ?> <tbody> <tr class="odd gradeX"> <td width="5%"><?php echo $user->getUserId(); ?></td> <td><?php echo $user->getName(); ?></td> <td><?php echo $user->getEmail(); ?></td> <td class="center">*** HOW DO I SHOW THE ROLE ?? ***</td> <td>Edit</td> </tr> <?php } ?>

How do I grab the role to show in the view?

Answer1:

You have to define the correct relationship between user and role in your entity definition. I am not sure why you use a linker table here. Do you want to actually have a many to many relationship between user and roles (every user can have several roles?). If not you can easily move the role id into the user table and then you define a ManyToOne relationship between user and role.

Your user table would then look like this:

------------------------------------------- |user_id | name | email | role_id | ------------------------------------------- | 1 | John | j@b.com | administrator | ------------------------------------------- | 2 | Bob | b@j.com | staff | -------------------------------------------

I would suggest to take a look at ManyToOne with user as the owning side. You can check how to properly define your unidirectional many to one relation inside your entity definition here in <a href="http://docs.doctrine-project.org/en/2.0.x/reference/association-mapping.html#many-to-one-unidirectional" rel="nofollow">the Doctrine2 documentation</a>

After that you can simply call $user->getRole(); in your view...

<h2>EDIT</h2>

Answer to fix a one to many using a join table:

This is also described in the <a href="http://docs.doctrine-project.org/en/2.0.x/reference/association-mapping.html#one-to-many-unidirectional-with-join-table" rel="nofollow">doctrine documentation here...</a>

You need three tables; a user table, a role table and a user-role-linker table The user is an entity, the role is an entity the role-linker table is not an entity in your case. You should drop that entity, the linker table is only used for connecting the user and role in the database.

<strong>User table</strong>

--------------------------- |id | name | email | --------------------------- | 1 | John | j@b.com | --------------------------- | 2 | Bob | b@j.com | ---------------------------

<strong>Role table</strong>

----------------- | id | ----------------- | administrator | ----------------- | staff | ----------------- | guest | ----------------- | another role | -----------------

<strong>Linker table</strong>

-------------------------- |user_id | role_id | -------------------------- | 1 | administrator | -------------------------- | 2 | staff | --------------------------

In your user entity:

/** ONE-TO-MANY UNIDIRECTIONAL, WITH JOIN TABLE ONLY WORK WITH MANY-TO-MANY ANNOTATION AND A UNIQUE CONSTRAINT * @ORM\ManyToMany(targetEntity="Administration\Entity\Role") * @ORM\JoinTable(name="user_role_linker", * joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")}, * inverseJoinColumns={@ORM\JoinColumn(name="role_id", referencedColumnName="id", unique=true)} * ) */ protected $roles; /** * Get roles. * * @return ArrayCollection */ public function getRoles() { return $this->roles; } /** * Add a role to the user. * * @param Role $role * * @return User */ public function addRole(Role $role) { $this->roles[] = $role; return $this; } /** * Remove a role from the user * * @param Role $role * * @return User */ public function removeRole(Role $role) { $this->roles->removeElement($role); return $this; }

Your role entity:

/** * An example entity that represents a role. * * @ORM\Entity * @ORM\Table(name="role") * @property string $id */ class Role { /** * @var string * @ORM\Id * @ORM\Column(type="string", length=255, unique=true, nullable=false) */ protected $id; /** * Get the id. * * @return string */ public function getId() { return $this->id; } }

I think this should help you solve it...

Maybe you should add the following to your application module:

public function doctrineValidate(MvcEvent $event){ $application = $event->getParam('application'); $serviceManager = $application->getServiceManager(); $entityManager = $serviceManager->get('doctrine.entitymanager.orm_default'); $validator = new SchemaValidator($entityManager); $errors = $validator->validateMapping(); if (count($errors) > 0) { // Lots of errors! var_dump($errors); } }

And then in bootstrap:

$eventManager->attach('dispatch', array($this, 'doctrineValidate'));

in module: Doctrine will help you by checking your entity definitions. It might tell you in advance that something is wrong in your entity definitions...

Answer2:

It's confusing, but as you defined in your entities, you could get the roles collection using <strong>User</strong> entity <strong>getRoleId</strong>'s function. Then, for each <strong>UserRoleLinker</strong> entity you have to use, again, the <strong>getRoleId</strong> function, which will return the 'Administrator' or 'Staff' string. Loop Example:

$roles = $user->getRoleId(); foreach ( $roles as $role ) { $roleId = $role->getRoleId(); }

I suggest you to do it another way. One entity should be <strong>User</strong>, with a property called <strong>roles</strong>. On the other side you could have the entity <strong>Role</strong>. The link between them should be a <a href="http://docs.doctrine-project.org/en/latest/reference/association-mapping.html#one-to-many-unidirectional-with-join-table" rel="nofollow"><strong>One-To-Many, Unidirectional with Join Table</strong></a> (which is the <strong>user_role_linker</strong> table).

Answer3:

As suggested by lluisaznar (although I need a many to 1 relationship since each user only has one role).

I am trying the following:

<?php namespace Administration\Entity; //use stuff /** @ORM\Entity */ class User { /** * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") * @ORM\Column(type="integer",name="user_id") */ //setters getters /** * @ManyToOne(targetEntity="Role") * @JoinColumn(name="user_id", referencedColumnName="id") */ private $role; public function __construct() { $this->role = new ArrayCollection(); } /** @return Collection */ public function getRoleId() { return $this->role; } }

And the Role entity:

<?php namespace Administration\Entity; use Doctrine\ORM\Mapping as ORM; /** @ORM\Entity */ class Role { /** * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") * @ORM\Column(type="integer",name="user_id") */ protected $user_id; /** @ORM\Column(type="string", name="role_id") */ protected $role; /** @param User|null */ public function getRoleId() { return $this->role; } }

When I run this I get the following notices:

Notice: Undefined index: id in trunk/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2611

and

Notice: Undefined index: user_id in trunk/vendor/doctrine/common/lib/Doctrine/Common/Proxy/AbstractProxyFactory.php on line 121

Also the role is not being displayed, when I dump the $user object under role I get this:

private 'role' => object(DoctrineORMModule\Proxy\__CG__\Administration\Entity\Role)[609] public '__initializer__' => object(Closure)[595] public '__cloner__' => object(Closure)[596] public '__isInitialized__' => boolean false protected 'user_id' => null protected 'role' => null

Recommend

  • Printing a selection of a JSON array data out to a page using JQuery
  • iTextSharp inserting spaces within words from a pdf file
  • Using a CASE with the IN clause in T-SQL
  • How to define many-to-many to itself in JPA?
  • avoid hibernate lazy initialization exception
  • How to get latitude and longitude position that stored in MySQL and use it in Android map applicatio
  • Why am I getting a (401) Unauthorized Error in Google Calendar API
  • Hibernate Creating Unwanted Mapping Tables
  • Entity Not Found error in symfony2 Form
  • onetomany unidirectional with jointable setup using jpa
  • How to not return an specific column using PagingAndSortingRepository
  • Symfony2 Change checkbox values from 0/1 to 'no'/'yes'
  • StackOverflow exception when using custom JsonConverter and custom ContractResolver
  • Multiple @ManyToMany sets from one join table
  • PrePersit current user
  • Push notification applicationIconBadgeNumber is not update in ios7
  • How to select full range in excel vba
  • vim tabular plugin question
  • many to many association. Datas doesn't delete
  • Symfony2 - ReferencedColumnName id is null
  • Hibernate Criteria with self join
  • Doctrine join bypass lazy loading
  • Can't insert value to mapped column in Doctrine 2 in OneToOne relation
  • 1 to 1..0 relationship in an embedded form with doctrine
  • Javascript how to filter arrays matching a keyword
  • How to remove relationship between many to many table with JPA and Hibernate
  • How to set the id of a foreign key id #sf2 #doctrine2
  • Hibernate one-to-many mapping eager fetch not working
  • How to make a user wait with Laravel
  • Find unique tuples in a relation represented by a BDD
  • Slowly changing dimension - What is Pure type 6 implementation
  • SQL append distinct values from two columns and two tables
  • Hibernate: Inheritance and relationship mapping + generics
  • Should I use composite primary keys in Grails?
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • jquery mobile loadPage not working
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How to stop GridView from loading again when I press back button?