Cakephp检索HABTM的条件 [英] Cakephp retrieving HABTM which Conditions

问题描述

我使用cakephp并且想显示属于'X'类的所有提交
我有4个表与HABTM关系。

Users - >（haveMany） - > Submissions < - >（hasAndBelongsToMany）< - > Categories

但我想这样做使用$ this->

用户表

  Id |名称
 ----- + ------------------- 
 1 |用户1 
 2 |用户2 
  

提交表

  Id |名称| User_id 
 ----- + ------------------- + -------------- 
 1 |提交1 | 1 
 2 |提交2 | 2 
  

类别表

  Id |名称
 ----- + ------------------- 
 1 |类别1 
 2 |类别2 
  

SubmissionCategory表

  Id | Submission_id | Category_id 
 ----- + ------------------- + ------------------- 
 1 | 1 | 1 
 2 | 1 | 2 
 3 | 2 | 1 
  

我真的很麻烦，创建一个分页可以做到这一点，我开始认为除非我缺少一些东西。

如果我没有使用cakephp这是我想要的查询。

  SELECT 
 * 
 FROM 
 submissions_categories，
提交，
用户
 WHERE 
 submissions_categories.category_id = 8 
 AND 
 submissions_categories.submission_id = submissions.id 
 AND 
 submissions.user_id = users.id 
  
 <？php 
 class SubmissionsController extends AppController {
 
 var $ name ='提交'; 
 var $ helpers = array（'Html'，'Form'）; 
 var $ paginate = array（'joins'=> array（
 array（
'table'=>'submissions_categories'，
'alias'=>'SubmissionsCategory '，
'type'=>'inner'，
'conditions'=> array（'SubmissionsCategory.submission_id = Submission.id'）
），
 array 
'table'=>'categories'，
'alias'=>'Category'，
'type'=>'inner'，
'conditions' > array（
'Category.id = SubmissionsCategory.category_id'
）
）））; 
 
 function index（）{
 $ this-> Submission-> recursion = 1; 
 $ this-> set（'submissions'，$ this-> paginate（array（'Category.id'=> 1）））; 
} 
} 
 
？> 
  
 
I'm using cakephp and would like to display all Submissions which are part of Category 'X'
I have 4 tables with a HABTM relationship.

Users -> (haveMany) -> Submissions <-> (hasAndBelongsToMany) <-> Categories

but I would like to do so using the $this->paginate() and for each submission I would like to display the user who posted the submission.

User Table
Id   |      Name         
-----+-------------------
1    |   User 1      
2    |   User 2
Submission Table
Id   |      Name         |   User_id
-----+-------------------+--------------
1    |   Submission 1    |      1    
2    |   Submission 2    |      2
Category Table
Id   |      Name 
-----+-------------------
1    |   Category 1        
2    |   Category 2
SubmissionCategory Table
Id   |   Submission_id   |   Category_id 
-----+-------------------+-------------------
1    |         1         |        1     
2    |         1         |        2  
3    |         2         |        1
I am having really trouble creating a paginate which can do this, I'm starting to think its not possible unless I'm missing something.

If I was not using cakephp this is the query I would want to do
SELECT 
     * 
FROM 
     submissions_categories, 
     submissions, 
     users
WHERE 
     submissions_categories.category_id = 8 
          AND 
     submissions_categories.submission_id = submissions.id 
          AND 
     submissions.user_id = users.id

解决方案
HABTM relationships are very unwieldy in CakePHP I find. You are going to need to set up the joins manually using the $paginate variable. Then you can pass in the optional conditions array to the paginate() function. Example:
<?php
class SubmissionsController extends AppController {

 var $name = 'Submissions';
 var $helpers = array('Html', 'Form');
 var $paginate = array('joins' => array(
     array( 
               'table' => 'submissions_categories', 
               'alias' => 'SubmissionsCategory', 
               'type' => 'inner',  
               'conditions'=> array('SubmissionsCategory.submission_id = Submission.id') 
           ), 
           array( 
               'table' => 'categories', 
               'alias' => 'Category', 
               'type' => 'inner',  
               'conditions'=> array( 
                   'Category.id = SubmissionsCategory.category_id'
               ) 
           )));

 function index() {
  $this->Submission->recursion = 1;
  $this->set('submissions', $this->paginate(array('Category.id'=>1)));
 }
}

?>


                        
这篇关于Cakephp检索HABTM的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！