Geek Answers Handbook

How to find conditions in two join tables in CakePHP

In my CakePHP application, I have three tables:

Businesses , Towns and Categories .

A business can belong to several cities and several categories, so I created a union of tables and hasMany and belongsTo relations. Everything works fine when searching for a business by city or category, using the model of the city or category to search for, but I'm completely stuck when I want to search for businesses in a specific city and a certain category, for example. Plumbers in London.

Associations do not seem to work when searching in a business model, and I get column not found errors when trying to use related tables. I would think that this would be in accordance with what needs to be done, but I can't get it to work:

 $this->set('listings', $this->Business->find('all', array( 'conditions' => array( 'Business.approved' => 1, 'BusinessesCategory.category_id' => $id, 'BusinessesTown.town_id' => $town_id, 'Business.sasite' => 1 )
+4
source share
3 answers

To do this, you need to join the tables. I will give an example of how to work with a category, and you can make a city yourself.

 $this->Business->find("all", array( "joins" => array( array( "table" => "businness_categories", "alias" => "BusinessesCategory", "type" => "LEFT", "conditions" => array( "Businesses.id = BusinessesCategory.business_id" ) ), array( "table" => "categories", "alias" => "Category", "type" => "LEFT", "conditions" => array( "BusinessesCategory.category_id = Category.id" ) ) ), 'conditions' => array( 'Business.approved' => 1, 'Category.id' => $id, ) ));

You can also use the behavior for this: https://github.com/Scoup/SuperJoin

+6
source

Hi, I had a very similar setup and the same problem. Here is how I would solve your problem:
Since you are not giving away most of your code, I make some assumptions:
- You have implemented your search method in BusinessController
- Your search argument for the city is stored in vaiable $, where for the category is stored in $ what

Code if you only have conditions for one table

  $this->Businesses->Town->recursive = -1; .... $options['joins'] = array( array('table' => 'towns', 'alias' => 'Town', 'type' => 'inner', 'conditions' => array( 'Business.town_id = Town.id', ) ) ); $options['conditions'] = array( 'Town.townName' => $where ); $result = $this->Business->find('all', $options);

Code if you have conditions for two tables

  $this->Businesses->Town->recursive = -1; $this->Businesses->Category->recursive = -1; .... $options['joins'] = array( array('table' => 'towns', 'alias' => 'Town', 'type' => 'inner', 'conditions' => array( 'Business.town_id = Town.id', ) ), array('table' => 'categories', 'alias' => 'Category', 'type' => 'inner', 'conditions' => array( 'Business.category_id = category.id', ) ) ); $options['conditions'] = array( 'Town.townName' => $where, 'Category.categoryName' => $what ); $result = $this->Business->find('all', $options);
+2
source

you can use

 $this->Business->find('all', array( 'conditions' => array( 'AND' => array( 'BusinessesTown.town_id' => $town_id, 'BusinessesCategory.category_id' => $id ) ), 'recursive' => 2 ));
0
source

More articles:


All Articles