Select query with doctrine

Question

I was trying to create a select query with doctrine but it didn't work. I have three tables, Product(ID, Name), Client(ID, Name), Orders(product, client). I need to select and show all the orders made by a client, and also the product name. How can I make this particular query using doctrine? Sorry if it's a banal question...

Produt:

<?php
namespace Example\Bundle\CrudBundle\Entity;
use Doctrine\ORM\Mapping as ORM;

/**
 * Product
 */
class Product
{
/**
 * @var string
 */
private $name;

/**
 * @var string
 */
private $prize;

/**
 * @var integer
 */
private $id;


/**
 * Set name
 *
 * @param string $name
 * @return Product
 */
public function setName($name)
{
    $this->name = $name;

    return $this;
}

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

/**
 * Set prize
 *
 * @param string $prize
 * @return Product
 */
public function setPrize($prize)
{
    $this->prize= $prize;

    return $this;
}

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

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

Client:

<?php
namespace Example\Bundle\CrudBundle\Entity;
use Doctrine\ORM\Mapping as ORM;

/**
* Client
*/
class Client
{
/**
 * @var string
 */
private $name;

/**
 * @var string
 */
private $age;

/**
 * @var integer
 */
private $id;


/**
 * Set name
 *
 * @param string $name
 * @return Client
 */
public function setName($name)
{
    $this->name = $name;

    return $this;
}

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

/**
 * Set age
 *
 * @param string $age
 * @return Client
 */
public function setAge($age)
{
    $this->age = $age;

    return $this;
}

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

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

}

Orders:

<?php
namespace Example\Bundle\CrudBundle\Entity;
use Doctrine\ORM\Mapping as ORM;

/**
 * Orders
 */
class Orders
{
/**
 * @var integer
 */
private $idProduct;

/**
 * @var integer
 */
private $idIngrediente;


/**
 * Set idProduct
 *
 * @param integer $idProduct
 * @return Orders
 */
public function setIdProduct($idProduct)
{
    $this->idProduct = $idProduct;

    return $this;
}

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

/**
 * Set idClient
 *
 * @param integer $idClient
 * @return Orders
 */
public function setIdClient($idIngrediente)
{
    $this->idClient= $idClient;

    return $this;
}

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

This is how I was trying to make the query, by first checking if the product is in the table and then selecting the fields I need from both the Orders and the Product tables.

class ProductRepository extends EntityRepository
{
    public function findAllOrderedByName()
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT * FROM ExampleCrudBundle:Product p ORDER BY p.name ASC'
            )
            ->getResult();
    }
}


public function findOneByIdJoinedToCategory($id)
{
    $query = $this->getEntityManager()
        ->createQuery('
            SELECT p, c FROM ExampleCrudBundle:Product p
            JOIN p.category c
            WHERE p.id = :id'
        )->setParameter('id', $id);

    try {
        return $query->getSingleResult();
    } catch (\Doctrine\ORM\NoResultException $e) {
        return null;
    }
}

Answer 1

1. you might want to use $em->getRepository("ExampleCrudBundle:Product")->createQueryBuilder('p') ->leftJoin("p.category c") ;

2. You need to read more about doctrine, on one hand, you dont need to define the table for ManyToMany relation, doctrine will do it for you, on the other hand, defining such table isn't enough as you need to show doctrine that there is a relation: product - category.

What you do now is:

"Get me a product where category is X" but there is no information about category in your product entity

Please read http://docs.doctrine-project.org/en/2.0.x/reference/association-mapping.html about "ManyToMany" relation, and relations at all.

Answer 2

Have you read the documentation?

http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html

This is how you do a select query:

<?php
$query = $em->createQuery('SELECT u FROM MyProject\Model\User u WHERE u.age > 20');
$users = $query->getResult();