We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Multiple joining

Hi All,

first of all I want to say that I'm a beginner in Phalcon. The problem with I stucked is about relation between some tables. Let's say that we have following tables: Users, Books, BookPages and the relation table RelUsersBooks. Each user can have defined acces to many books and each book can be accesed by many users. There can be also Admin user who has acces to all books. The problem is that I need to select all book pages from all books that user has acces to. Is there any easy or 'right' approach to do so? My application needs many objects like BookPages ( like i.e. BookToc, BookIndex, ...) so in general I look for easy solution (not so performance tuned) to I could easy and fast add new models. Thanks in advance :)



17.5k

Hello,

To achieve multiple joining I use $this->modelsManager->createBuilder(); It's a powerfull mechanism to create complex queries using PHQL.

I have no idea how your tables looks, but in my case I have a situation to show articles with a ManytoMany relation on contacts and the contacts are related to a country. To show all articles related to a country, I have a query like this.

                $articles=$this->modelsManager->createBuilder()
                    ->columns(array('Articles.id', 'Articles.listtitle', 'Articles.listdescription', 'Articles.filename', 'Articles.adddate', 'Articles.modifydate', 'Articles.publishdate', 'Articles.expiredate', 'Users.username', 'Users.realname', 'Images.path', 'Images.thumbnail'))
                    ->from('Articles')
                    ->innerJoin('Categoryarticles', 'Articles.id=Categoryarticles.articleid')
                    ->innerJoin('Categories', 'Categoryarticles.categoryid=Categories.id')
                    ->innerJoin('Contactarticles', 'Articles.id=Contactarticles.articleid')
                    ->innerJoin('Contacts', 'Contactarticles.contactid=Contacts.id')
                    ->innerJoin('Countries', 'Contacts.visitingcountryid=Countries.id OR Contacts.postofficeboxcountryid=Countries.id')
                    ->innerJoin('Users', 'Articles.publishid=Users.id')
                    ->leftJoin('Images', 'Articles.iconid=Images.id')
                    ->where('Countries.id=:id:',array('id' => $country->id))
                    ->andWhere('Articles.languagekey=:languagekey:',array('languagekey' => $this->languagekey()))
                    ->andWhere('Articles.trash=:trash:',array('trash' => 'no'))
                    ->andWhere('Articles.publishdate<=:date:',array('date' => date('Y-m-d')))
                    ->andWhere('Articles.expiredate>:date:',array('date' => date('Y-m-d')))
                    ->andWhere('Categories.trash=:trash:',array('trash' => 'no'))
                    ->andWhere('Categories.languagekey=:languagekey:',array('languagekey' => $this->languagekey()))
                    ->andWhere('Contacts.trash=:trash:',array('trash' => 'no'))
                    ->andWhere('Contacts.languagekey=:languagekey:',array('languagekey' => $this->languagekey()))
                    ->andWhere('Users.trash=:trash:',array('trash' => 'no'))
                    ->andWhere('Users.languagekey=:languagekey:',array('languagekey' => $this->languagekey()))
                    ->groupBy(array('Articles.id'))
                    ->orderBy('Articles.listtitle ASC')                 
                    ->getQuery()
                    ->execute();

Hopefully it helps you a bit to create your own queries.



1.8k

Thanks, works like charm ;)