DQL join tables (Doctrine)

module/module name/view/module name/list.phtml

<tbody>
<!–?php foreach($strollingsalearea as $post) : ?>
<tr>
php echo $post->getNameEng();?>”>getNameTh();?>
</tr>
<!–?php endforeach; ?>
</tbody>

Controller :

$entityManager = $this->getServiceLocator()->get(‘doctrine.entitymanager.orm_default’);
$dql = “SELECT c FROM Saletarget\Entity\CfgRoleResp a
JOIN Saletarget\Entity\StRollingSaleArea b WITH a.areaId = b.areaId
JOIN Saletarget\Entity\CfgProductHc c WITH b.productNodeId = c.productNodeId WHERE a.roleId = “. $num .” “;
$query = $entityManager->createQuery($dql);
$data = $query->getResult();

Entity CfgRoleResp :

class CfgRoleResp
{
/**
* @var integer
*
* @ORM\Column(name=”resp_id”, type=”integer”, nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy=”IDENTITY”)
*/

private $respId;

/**
* @var integer
*
* @ORM\Column(name=”role_id”, type=”integer”, length=10, nullable=false)
*/
private $roleId;

/**
* @var integer
*
* @ORM\Column(name=”area_id”, type=”integer”, length=10, nullable=false)
*/
private $areaId;

}

Entity StRollingSaleArea :

class StRollingSaleArea
{
//strategy=”IDENTITY”
//strategy=”AUTO”
/**
* @var integer
*
* @ORM\Column(name=”Id”, type=”integer”, nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy=”IDENTITY”)
*/
private $rollingId;

/**
* @var integer
*
* @ORM\Column(name=”area_id”, type=”integer”, length=10, nullable=false)
*/
private $areaId;

/**
* @var integer
*
* @ORM\Column(name=”product_node_id”, type=”integer”, length=10, nullable=false)
*/
private $productNodeId;

}

Entity CfgProductHc :

class CfgProductHc
{
/**
* @var integer
*
* @ORM\Column(name=”node_id”, type=”integer”, nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy=”IDENTITY”)
*/
private $productNodeId;

}

Credit by : http://www.iDevelopper.net

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s