CODEWITHSUNDEEP
zend-frameworkdoctrine
Developer
Developer

Reputation: 26223

How can I build this query in doctrine Zend

I have all the models. I need to define relation in doctrine and build a query using doctrine.

Query without doctrine which works fine. 

SELECT * FROM asset_site_link l
join assets a on l.assetID = a.id
join assetTypes t on t.id = a.assetTypeID
join assetCategories c on c.id = a.categoryID
where t.name="image" AND c.name = "static_banner"
and l.siteID = "2"

My First Attept is something like this, which did not work. 

 $q = Doctrine_Query::create()
    ->select('r.*')
    ->from('assetManagement_Model_asset r')
    ->leftJoin('r.assetTypeID t')
    ->leftJoin('r.categoryID c')  
    ->leftJoin('r.assetSiteLink l')                        
    ->where('r.isDeleted = 0')
    ->andWhere('t.name = ?', "image")
    ->andWhere('c.name = ?', "static_banner")
    ->andWhere ('l.siteID = ?', "2");

while below query is working fine (without assetSiteLink join)

 $q = Doctrine_Query::create()
    ->select('r.*')
    ->from('assetManagement_Model_asset r')
    ->leftJoin('r.assetTypeID t')
    ->leftJoin('r.categoryID c')  
    ->where('r.isDeleted = 0')
    ->andWhere('t.name = ?', "image")
    ->andWhere('c.name = ?', "static_banner");

Just to tell you that Asset model has one to Many Relation with AssetSiteLink

Any Idea?

Upvotes: 0

Views: 870

Answers (2)

vestigal
vestigal

Reputation: 71

If anyone is wondering why the code that is above won't execute for them, it might be due to using the "andWhere()" function. In my version of the Zend Framework, there is no "andWhere()" function; you just call where() twice, instead.

This means that, for me, the above piece of code would need to be written like so:

q = Doctrine_Query::create()
        ->select('r.*, l.*')
        ->from('linkManagement_Model_assetSiteLink l')
        ->leftJoin('l.assetSiteLink r')                        
        ->leftJoin('r.assetTypeID t')
        ->leftJoin('r.categoryID c')              
        ->where('r.isDeleted = 0')
        ->where('t.name = ?', "image")
        ->where('c.name = ?', "static_banner")
        ->where('l.siteID = ?', "2")

Upvotes: 1

Developer
Developer

Reputation: 26223

   $q = Doctrine_Query::create()
        ->select('r.*, l.*')
        ->from('linkManagement_Model_assetSiteLink l')
        ->leftJoin('l.assetSiteLink r')                        
        ->leftJoin('r.assetTypeID t')
        ->leftJoin('r.categoryID c')              
        ->where('r.isDeleted = 0')
        ->andWhere('t.name = ?', "image")
        ->andWhere('c.name = ?', "static_banner")
        ->andWhere('l.siteID = ?', "2")
   ;

Upvotes: 1

Related Questions