Join in Doctrine Query Language

Question

I'm having trouble converting sql to dql. This what I trying to achieve when the users are in activity table they should not be able to edit an inactive state but state is located in another table.

SQL

SELECT Activity1.Payrollperiodid, PayrollPeriod.state
FROM Activity1
INNER JOIN PayrollPeriod ON Activity1.payrollperiodid = Payrollperiod.payrollperiodid
where PayrollPeriod.state=1;

DQL

 public function findBytransid($payrollperiodid)
{
  $repository=$this->getEntityManager()->getRepository('comtwclagripayrollBundle:Activity');
  $qb = $repository->createQueryBuilder('a');
  $qb->select('a');
  $qb->innerJoin('a.payrollperiodid', 'p');
  $qb->where('a.payrollperiodid=:payrollperiodid and a.status=:1');
  $qb->setParameter('payrollperiodid', $payrollperiodid);
  $qb->setParameter('status', 1);
  return $results = $qb->getQuery()->getResult();

Change `:1` to `:status` – Jason Roman Aug 18 '17 at 21:09 — Jason Roman, Aug 18 '17 at 21:09

score 0 · Answer 1 · answered Aug 18 '17 at 20:58

0

Same problem with answer

Maybe this solve your problem

$em = $this->getEntityManager();    
$qb =  $em->getRepository('comtwclagripayrollBundle:Activity')
        ->createQueryBuilder('a');

return $qb
    ->select('a')
    ->innerJoin('a.payrollperiodid', 'p',Join::WITH,  $qb->expr()->eq('a.payrollperiodid',':id'))
    ->where($qb->expr()->eq('a.status',':status'))
    ->setParameters([
        'id'=>$payrollperiodid,
        'status' =>1
    ])
    ->getQuery()
    ->getResult();

answered Aug 18 '17 at 20:58

Ivan Borysenko

235
1
2
12

I get this error: Attempted to load class "Join" from namespace – Sue Aug 18 '17 at 21:13
replace Join::WITH to 'WITH' – Ivan Borysenko Aug 18 '17 at 21:20
1

[Read about Joins in documentation](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html) – Ivan Borysenko Aug 18 '17 at 21:31

score 0 · Answer 2 · answered Aug 18 '17 at 21:49

$repository=$this->getEntityManager()->getRepository('comtwclagripayrollBundle:Activity');
        $qb = $repository->createQueryBuilder('a');
        $qb->select('a','pp');
        $qb->Join('a.payrollperiodid','pp');
        $qb->where('pp.state = :state');
        $qb->setParameter('state', 0);

        return $results = $qb->getQuery()->getResult();

Join in Doctrine Query Language

2 Answers2