Symfony2 QueryBuilder SQL to DQL

Question

can anybody help me converting my SQL into valid Doctrine DQL Code?

SELECT * FROM tasks WHERE (SELECT COUNT(*) FROM tasks_done WHERE tasks_done.user_id = $userid AND tasks_done.task_id = tasks.id) = 1 AND tasks.user = $userid

What I tried:

 $em = $this->getDoctrine()->getManager();

    $qb = $em->createQuery("
        SELECT
            t
        FROM
            tasks t
        WHERE
            t.id IN (
                SELECT
                    td
                FROM
                    TaskDone td
                WHERE
                    td.task_id = t.id
                AND
                    td.user_id = $id
            )

        AND
           t.user = $id
    ");
    $taskDone = $qb->getResult();

This throws following exception:

[Semantical Error] line 0, col 71 near 'tasks t ': Error: Class 'tasks' is not defined.

My Entites are called: Task.php and TaskDone.php , Classname is the same like filename. Table names are: tasks and task_done

EDIT

Structure TaskDone.php Entite

<?php

namespace Seotool\MainBundle\Entity;

use Doctrine\Common\Collections\ArrayCollection; use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="task_done")
 */
class TaskDone
{


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


/**
 * Get id
 *
 * @return integer
 */
public function getId()
{
    return $this->id;
}

/**
 * @ORM\Column(type="integer")
 */
private $task_id;

/**
 * @ORM\Column(type="integer")
 */
private $user_id;

/**
 * Set task_id
 *
 * @param integer $taskId
 * @return TaskDone
 */
public function setTaskId($taskId)
{
    $this->task_id = $taskId;

    return $this;
}

/**
 * Get task_id
 *
 * @return integer 
 */
public function getTaskId()
{
    return $this->task_id;
}

/**
 * Set user_id
 *
 * @param integer $userId
 * @return TaskDone
 */
public function setUserId($userId)
{
    $this->user_id = $userId;

    return $this;
}

/**
 * Get user_id
 *
 * @return integer 
 */
public function getUserId()
{
    return $this->user_id;
}

}

Structure Task.php Entitie

<?php

namespace Seotool\MainBundle\Entity;
use Doctrine\Common\Collections\ArrayCollection;
use Symfony\Component\Validator\Constraints as Assert;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="tasks")
 */
class Task {

/**
 * @ORM\Column(type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
 * @ORM\Column(type="string")
 */
protected $task_title;

/**
 * @ORM\Column(type="text")
 */
protected $task_description;


/**
 * @ORM\Column(type="string")
 */
protected $task_priority;

/**
 * @ORM\ManyToOne(targetEntity="TaskTypes", inversedBy="task")
 * @ORM\JoinColumn(name="tasktype", referencedColumnName="id")
 */
protected $TaskTypes;

/**
 * @ORM\ManyToOne(targetEntity="User", inversedBy="task")
 * @ORM\JoinColumn(name="user", referencedColumnName="id")
 */
protected $User;

/**
 * Get id
 *
 * @return integer 
 */
public function getId()
{
    return $this->id;
}

/**
 * Set task_title
 *
 * @param string $taskTitle
 * @return Task
 */
public function setTaskTitle($taskTitle)
{
    $this->task_title = $taskTitle;

    return $this;
}

/**
 * Get task_title
 *
 * @return string 
 */
public function getTaskTitle()
{
    return $this->task_title;
}

/**
 * Set task_description
 *
 * @param string $taskDescription
 * @return Task
 */
public function setTaskDescription($taskDescription)
{
    $this->task_description = $taskDescription;

    return $this;
}

/**
 * Get task_description
 *
 * @return string 
 */
public function getTaskDescription()
{
    return $this->task_description;
}

/**
 * Set task_priority
 *
 * @param string $taskPriority
 * @return Task
 */
public function setTaskPriority($taskPriority)
{
    $this->task_priority = $taskPriority;

    return $this;
}

/**
 * Get task_priority
 *
 * @return string 
 */
public function getTaskPriority()
{
    return $this->task_priority;
}


/**
 * Set TaskTypes
 *
 * @param \Seotool\MainBundle\Entity\TaskTypes $taskTypes
 * @return Task
 */
public function setTaskTypes(\Seotool\MainBundle\Entity\TaskTypes $taskTypes = null)
{
    $this->TaskTypes = $taskTypes;

    return $this;
}

/**
 * Get TaskTypes
 *
 * @return \Seotool\MainBundle\Entity\TaskTypes 
 */
public function getTaskTypes()
{
    return $this->TaskTypes;
}

/**
 * Set User
 *
 * @param \Seotool\MainBundle\Entity\User $user
 * @return Task
 */
public function setUser(\Seotool\MainBundle\Entity\User $user = null)
{
    $this->User = $user;

    return $this;
}

/**
 * Get User
 *
 * @return \Seotool\MainBundle\Entity\User 
 */
public function getUser()
{
    return $this->User;
}


}

EDIT 2

Current code of my controller, which result is an empty array:

$em = $this->getDoctrine()->getManager();

    $qb = $em->createQuery("
        SELECT
            t
        FROM
            SeotoolMainBundle:Task t
        WHERE
            t.id IN (
                SELECT
                    td
                FROM
                    SeotoolMainBundle:TaskDone td
                WHERE
                    td.task_id = t.id
                AND
                    td.user_id = $id
            )

        AND
           t.User = $id
    ");
    $taskDone = $qb->getResult();
    $done = print_r($taskDone, true);

Table TaskDone has entries ( ID 1 | Task ID 36 | User ID 13 ) Task with ID 36 exists.

Answer 1

I think your query should be more simple.

What do you want exactly? All of the tasks which has been done by a specific user?

So, if you join these two tables and filter on the taskDone.user, it should works.

Let's try this.

SELECT 
    t
FROM
    SeotoolMainBundle:Tasks t, SeotoolMainBundle:TaskDone td
WHERE
    t.id=td.task_id
AND
    td.user = $userid

(be careful about the names I gave to your tables, I'm not sure)

Perhaps, I did not understand what do you want. If it the case, please be more accurate ;)