dongqing220586 2014-05-19 02:01
浏览 18

如何在内部连接表上应用DISTINCT

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 
  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 linux驱动,linux应用,多线程
    • ¥20 我要一个分身加定位两个功能的安卓app
    • ¥15 基于FOC驱动器,如何实现卡丁车下坡无阻力的遛坡的效果
    • ¥15 IAR程序莫名变量多重定义
    • ¥15 (标签-UDP|关键词-client)
    • ¥15 关于库卡officelite无法与虚拟机通讯的问题
    • ¥15 目标检测项目无法读取视频
    • ¥15 GEO datasets中基因芯片数据仅仅提供了normalized signal如何进行差异分析
    • ¥100 求采集电商背景音乐的方法
    • ¥15 数学建模竞赛求指导帮助