I have the following entity, basically it says a picture has a shop
class InstagramShopPicture
{
/**
* @Exclude()
* @ORM\ManyToOne(targetEntity="InstagramShop", inversedBy="userPictures")
* @ORM\JoinColumn(name="shop_id", referencedColumnName="id", nullable=false, onDelete="CASCADE")
*/
protected $shop;
}
how do I query 10 pictures, each from a unique store in DQL?
So far I have the following:
$query = $em->createQueryBuilder()->select('picture')
->from("ShopiousMainBundle:InstagramShopPicture", 'picture')
->innerJoin('picture.shop', 'shop')
->setMaxResults(10);
;
in other words I just wanted DISTINCT shop, but I am finding it hard to transalate DISTINCT into DQL
UPDATE:
$query = $em->createQueryBuilder()
->select('pictures')
->from("AppMainBundle:InstagramShop", 'shop')
->innerJoin('shop.userPictures', 'pictures')
->innerJoin('pictures.category', 'category')
->innerJoin('category.pictureFirstLevelCategory', 'firstLevelCategory')
->distinct()
;
$query->andWhere('firstLevelCategory = :firstLevelCategory');
$query->andWhere('shop.isLocked = false');
$query->andWhere('shop.expirydate IS NOT NULL');
$query->andWhere('shop.id != :shopId');
$query->andWhere('shop.expirydate >= :currentDate');
$parameter["currentDate"] = new \DateTime('now');
$parameter["shopId"] = $shopId;
$parameter["firstLevelCategory"] = $picture->getCategory()->getPicturefirstlevelcategory()();
I tried doing the above and it gives me this error:
Error: Cannot select entity through identification variables without choosing at least one root entity alias.
UPDATE:
SELECT DISTINCT pictures FROM AppMainBundle:InstagramShop shop INNER JOIN shop.userPictures pictures INNER JOIN pictures.category category INNER JOIN category.pictureFirstLevelCategory firstLevelCategory WHERE firstLevelCategory = :firstLevelCategory AND shop.isLocked = false AND shop.expirydate IS NOT NULL AND shop.id != :shopId AND shop.expirydate >= :currentDate