How to get Entities based on field values in another Entitiy in Doctrine2?

Question

I have entity Product and entity Subcategory:

Subcategory.php

namespace Project\Entities;    

/**
 * Subcategory 
 * @Entity
 * @Table(name="subcategories")
 *
 */
class Subcategory {
/**
 * 
 * @Id 
 * @GeneratedValue 
 * @Column(type="integer")
 * @var integer $id
 */
public $id;

/**
 * @Column(type="string")
 * @var string $name
 */
public $name;
}

Product.php

namespace Project\Entities;

/**
 * 
 * Product 
 * @Entity
 * @Table(name="products")
 */
class Product {
    /**
     * 
     * @Id 
     * @GeneratedValue 
     * @Column(type="integer",length=16)
     * @var integer $id
     */
    public  $id;

    /**
     * 
     * @Column(type="integer",length=3)
     * @var integer $id_subcat
     */
    public $id_subcat;

    /**
     * 
     * @Column(type="string")
     * @var integer $name
     */
    public $name;


    /**
     * 
     * @Column(type="string")
     * @var integer $available
     */
    public $available;
}

I would like to get all Subcategories that have Products available field equal 1. Ergo. Get Subcategories that have only available products in them.

I know how to write SQL query for that but I don't know how to achieve this in DQL.

Should I provide some additional annotation?

Answer 1

You have to begin by declaring the relationship in your entities. I am going to assume that your Subcategory can have many Products (one-to-many):

I haven't tested this code obviously, so I apologize if there are some errors, this is off the top of my head mostly.

/**
 * Subcategory 
 * @Entity
 * @Table(name="subcategories")
 *
 */
class Subcategory {
    /**
     * @OneToMany(targetEntity="Project\Entities\Product", mappedBy="id_subcat")
     * @var Products[]
     */
    protected $products;

    public function __construct()
    {
             $this->products = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // Leaving out rest of class for simplicity...
}


/**
 * 
 * Product 
 * @Entity
 * @Table(name="products")
 */
class Product {

    // Rest of class....

    /**
     * @ManyToOne(targetEntity="Project\Entities\Subcategory", inversedBy="products")
     * @JoinColumn(name="id_subcat", referencedColumnName="id")
     */
     protected $id_subcat = null;

}

** Then to query, in SF2, it would be like this:

/* var \Doctrine\ORM\EntityManager $em */
$dql = "SELECT p FROM Project\Entities\Product p JOIN p.subcategoryId sc ORDER BY p.name ASC"
$query = $em->createQuery($dql);
$results = $query->getResult();
$products = array();

Now, because we have protected variables in our entity, you'd want setters and getters on your objects. This can be very helpful for formatting how content is returned, etc.

foreach ($results as $product)
{
     $products[] = array("id" => $product->getId(),
                         "name" => $product->getName(),
                         "subCategory" => $product->getSubcategoryId()                           
                          );
 }