CakePHP多个JOIN findAll条件问题 [英] CakePHP multiple JOIN findAll Conditions issue
问题描述
这是我的复合体（至少我认为是复杂的）条件，从匹配赛程表和事件相关的竞争对手。
现在我有 HTBTM 与 events_competitors 表的关系，其中多个事件有多个竞争者用户条目。 / p>
这里，我使用 joins 条件加入并获取相关的事件与竞争对手这工作正常，但我还想应用附加条件， is_black 皮带）和 is_adult （检查成人）
'EventCompetitor。 is_black'=> 0，
'EventCompetitor.is_adult'=> 0，
在这里，我只想要那些同时具有两个条件（is_black / is_adult）
以下是我的整个查找条件：
$ matchdivisions = $ this->竞争对手 - > find（all，
数组（
'conditions'=>
数组（
'Competitor.status'=> 1，
'Competitor.payment_completed'=> 1，
'Competitor.weightgroup_id'=> $ current_matchsc ['Matchschedule'] ['weightgroup_id']，
'Competitor.rank_id'=> $ current_matchsc ['Matchschedule'] ['rank_id']，
'Competitor.degree_id'=> $ current_matchsc ['Matchschedule'] ['degree_id']，
'Competitor.gender'=> $ current_matchsc ['Matchschedule'] ['gender']，
），
'joins'=>
array（
array（
'table'=>'event_competitors'，
'alias'=>'EventCompetitor '，
'type'=>'left'，
'conditions'=> array（
AND=& $ b'EventCompetitor.event_id ='。$ current_matchsc ['Event'] ['id']，
'EventCompetitor.is_black'=> 0，
'EventCompetitor.is_adult'=&
）
），
）
），
'group'=> 'Competitor.id'
）
）;
任何想法，我如何将这些事情应用到 以下是SQL Dump for您的参考： SELECT JOIN 。
竞争对手。 id code>竞争者 first_name ，竞争者 last_name ，竞争者。 parent_name ，竞争者。性别，竞争者。 date_of_birth code>竞争者。 email_address ，竞争者重量，竞争者。 weightgroup_id ， $ c>。。 height ，竞争者。 rank_id code>竞争对手 <_id ，照片，竞争者。 school_id ， $ c>。。 years_of_experience ，竞争者。年龄 code>竞争者 tournament_id ，竞争者 total_registration_fees 竞争对手。 address1 ， $ c>。。 address2 ，竞争者。 city code>竞争者 zip_code ，竞争者 country_id ，竞争者。 state_id ， $ c>。。 phone_number ，竞争对手。 mobile_number code>竞争对手。 payment_mode ， email_sent ，竞争对手。 payment_completed ， $ c>。。状态，竞争者。创建 code>竞争者修改，排名。 id ，，。。 $ c>。。状态，排名。创建 code> ，，。 id ，锦标赛。锦标赛名称，锦标赛。 tournament_type ，比赛。 tournament_date code>比赛 venue_name ，比赛。 address1 ，比赛。 address2 ，比赛。城市，锦标赛。 zip_code code>锦标赛 country_id ，锦标赛。 state_id ，比赛。创建，比赛。修改，国家。 id code>国家。状态，，国家， $ c>。。修改，状态。 id code> State 。country_id ，名称，，州。 short_name ， $ c>。。状态，状态 code>州。 ， id ， Degree 。 rank_id ， Degree 。 ，， code>学位。。。 id ，学校。 $ c>。。 address1 ，学校。 address2 code>学校。，城市 zip_code 学校。 country_id ， $ c>。。 state_id ，学校。 phone_number code>学校 establishment_date ，学校。 total_competitors ，学校。 ，， code>学校。修改，交易。 id ，，， $ c> 。 。 code>交易。 ， mc_gross ，交易。 address_status ，交易。 payer_id ，交易。 address_street code>交易。 payment_date ， payment_status ，交易。 address_zip ，交易。 first_name ，交易。 address_country_code code> 。。。。自定义，交易。 payer_status ， $ c>。。 address_country ，交易。 address_city code>交易 payer_email ， verify_sign ，交易。 txn_id ，交易。 pay_type ，交易。 last_name code>交易 receiver_email ，交易。 item_name ， $ c>。。 mc_currency ，交易。 item_number code>交易 residence_country ， transaction_subject ，交易。 payment_gross ，交易。 ，。 code>交易。 pending_reason FROM 竞争者 AS 竞争对手 left JOIN event_competitors AS EventCompetitor ON（ EventCompetitor 。 event_id = 0 AND = 3 AND EventCompetitor 。 is_black / code> is_adult = 0）LEFT JOIN ranks AS code> ON（竞争者。 rank_id = 排名 id ）LEFT JOIN 比赛 AS 比赛 竞争者。 tournament_id = 比赛。 id ）LEFT JOIN 国家 AS 国家 。。 c>）LEFT JOIN 州 AS 州 。 state_id = State 。 id ）LEFT JOIN 度 AS 学位 ON（竞争者。 degree_id = 学位。 id ）LEFT JOIN code> AS 。学校 ON（竞争者。 school_id <= code>学校 id ）LEFT JOIN code>交易 ON（交易。 competitor_id = 竞争者。 id ）WHERE 竞争者。 code> = 1 AND 竞争对手。 payment_completed = 1 AND c $ c>。 weightgroup_id = 13 AND 竞争者。 rank_id = 11 AND 竞争者。 degree_id ='0'AND 竞争者 。 = < c>
以下是来自上述查询的连接条件ref：
left JOIN event_competitors AS EventCompetitor ON（EventCompetitor.event_id = 3 AND EventCompetitor.is_black = 0 AND EventCompetitor.is_adult = 0）
您应该为此使用可包含的行为。更多： http://book.cakephp.org/view/1323/Containable
- 将其添加到您的竞争对手模型中。
var $ actsAs = array（'Containable'）;
- 在竞争对手模型中更新您的模型关系，包括is_black和is_adult条件：
var $ hasAndBelongsToMany = array '=> array（
'className'=>'Competitor'，
'joinTable'=>'event_competitors'，
'alias'=>'EventCompetitor'，
' '=> array（
'EventCompetitor.is_black'=> 0，
'EventCompetitor.is_adult'=> 0
）
）
）;
3）要注入事件id，请将一个包含数组传递给find操作：
$ contains = array（
'EventCompetitor'=> array（
'conditions'=& EventCompetitor.event_id'=> $ current_matchsc ['Event'] ['id']）
）
）;
$ matchdivisions = $ this-> Competitor-> find（all，
array（
'contains'=> $ contain，
'conditions'=> ; array（
'Competitor.status'=> 1，
'Competitor.payment_completed'=> 1，
'Competitor.weightgroup_id'=> $ current_matchsc ['Matchschedule'] ['weightgroup_id']，
'Competitor.rank_id'=> $ current_matchsc ['Matchschedule'] ['rank_id']，
'Competitor.degree_id'=> $ current_matchsc ['Matchschedule'] ['degree_id']，
'Competitor.gender'=> $ current_matchsc ['Matchschedule'] ['gender']
）
）
）
如果关系不一定需要is_black和is_adult，模型，并根据需要通过find操作的contains参数传递它们。
Here is my complex (atleast i think it is complex) condition to find competitors from matches schedules and relating to events.
Now I have HTBTM relations with events_competitors table, where multiple events have multiple competitors users entries.
Here, I have used joins condition for joining and getting related events with competitors which works fine, but I also want to apply additional conditions, for is_black (check for black belt) and is_adult (check for adult person)
'EventCompetitor.is_black' => 0,
'EventCompetitor.is_adult' => 0,
Here I want only those competitors which have both conditions (is_black / is_adult) 0, means not eligible, but it does not applying the same, which is resulting in wrong competitors results.
Below is my whole find condition:
$matchdivisions = $this->Competitor->find("all" ,
array(
'conditions' =>
array(
'Competitor.status' => 1,
'Competitor.payment_completed' => 1,
'Competitor.weightgroup_id' => $current_matchsc['Matchschedule']['weightgroup_id'],
'Competitor.rank_id' => $current_matchsc['Matchschedule']['rank_id'],
'Competitor.degree_id' => $current_matchsc['Matchschedule']['degree_id'],
'Competitor.gender' => $current_matchsc['Matchschedule']['gender'],
),
'joins' =>
array(
array(
'table' => 'event_competitors',
'alias' => 'EventCompetitor',
'type' => 'left',
'conditions'=> array(
"AND" =>array(
'EventCompetitor.event_id = '.$current_matchsc['Event']['id'],
'EventCompetitor.is_black' => 0,
'EventCompetitor.is_adult' => 0,
)
),
)
),
'group' => 'Competitor.id'
)
);
Any idea, how can i get those things applied into JOIN conditions, so it is applied into results.
Thanks !
Below is SQL Dump for your ref:
SELECT Competitor.id, Competitor.first_name, Competitor.last_name, Competitor.parent_name, Competitor.gender, Competitor.date_of_birth, Competitor.email_address, Competitor.weight, Competitor.weightgroup_id, Competitor.height, Competitor.rank_id, Competitor.degree_id, Competitor.photo, Competitor.school_id, Competitor.years_of_experience, Competitor.age, Competitor.tournament_id, Competitor.total_registration_fees, Competitor.address1, Competitor.address2, Competitor.city, Competitor.zip_code, Competitor.country_id, Competitor.state_id, Competitor.phone_number, Competitor.mobile_number, Competitor.payment_mode, Competitor.email_sent, Competitor.payment_completed, Competitor.status, Competitor.created, Competitor.modified, Rank.id, Rank.name, Rank.status, Rank.created, Rank.modified, Tournament.id, Tournament.tournament_name, Tournament.tournament_type, Tournament.tournament_date, Tournament.venue_name, Tournament.address1, Tournament.address2, Tournament.city, Tournament.zip_code, Tournament.country_id, Tournament.state_id, Tournament.created, Tournament.modified, Country.id, Country.name, Country.status, Country.created, Country.modified, State.id, State.country_id, State.name, State.short_name, State.status, State.created, State.modified, Degree.id, Degree.rank_id, Degree.name, Degree.status, Degree.created, School.id, School.name, School.address1, School.address2, School.city, School.zip_code, School.country_id, School.state_id, School.phone_number, School.owner_name, School.establishment_date, School.total_competitors, School.status, School.created, School.modified, Transaction.id, Transaction.competitor_id, Transaction.noncompetitor_id, Transaction.created, Transaction.modified, Transaction.mc_gross, Transaction.address_status, Transaction.payer_id, Transaction.address_street, Transaction.payment_date, Transaction.payment_status, Transaction.address_zip, Transaction.first_name, Transaction.address_country_code, Transaction.address_name, Transaction.custom, Transaction.payer_status, Transaction.address_country, Transaction.address_city, Transaction.payer_email, Transaction.verify_sign, Transaction.txn_id, Transaction.payment_type, Transaction.last_name, Transaction.address_state, Transaction.receiver_email, Transaction.item_name, Transaction.mc_currency, Transaction.item_number, Transaction.residence_country, Transaction.transaction_subject, Transaction.payment_gross, Transaction.shipping, Transaction.test_ipn, Transaction.pending_reason FROM competitors AS Competitor left JOIN event_competitors AS EventCompetitor ON (EventCompetitor.event_id = 3 AND EventCompetitor.is_black = 0 AND EventCompetitor.is_adult = 0) LEFT JOIN ranks AS Rank ON (Competitor.rank_id = Rank.id) LEFT JOIN tournaments AS Tournament ON (Competitor.tournament_id = Tournament.id) LEFT JOIN countries AS Country ON (Competitor.country_id = Country.id) LEFT JOIN states AS State ON (Competitor.state_id = State.id) LEFT JOIN degrees AS Degree ON (Competitor.degree_id = Degree.id) LEFT JOIN schools AS School ON (Competitor.school_id = School.id) LEFT JOIN transactions AS Transaction ON (Transaction.competitor_id = Competitor.id) WHERE Competitor.status = 1 AND Competitor.payment_completed = 1 AND Competitor.weightgroup_id = 13 AND Competitor.rank_id = 11 AND Competitor.degree_id = '0' AND Competitor.gender = 'Female' GROUP BY Competitor.id
Here is the left join condition from above query for ref:
left JOIN event_competitors AS EventCompetitor ON (EventCompetitor.event_id = 3 AND EventCompetitor.is_black = 0 AND EventCompetitor.is_adult = 0)
You should be using the containable behavior for this. More at: http://book.cakephp.org/view/1323/Containable
- Add it to your Competitor model.
var $actsAs = array('Containable');
- Update your model relationships in your Competitor model to include the is_black and is_adult conditions:
var $hasAndBelongsToMany = array(
'Competitor' => array(
'className' => 'Competitor',
'joinTable' => 'event_competitors',
'alias' => 'EventCompetitor',
'conditions' => array(
'EventCompetitor.is_black' => 0,
'EventCompetitor.is_adult' => 0
)
)
);
3) To inject the event id, pass a contain array to your find operation:
$contain = array(
'EventCompetitor' => array(
'conditions' => array('EventCompetitor.event_id' => $current_matchsc['Event']['id'])
)
);
$matchdivisions = $this->Competitor->find("all" ,
array(
'contain' => $contain,
'conditions' => array(
'Competitor.status' => 1,
'Competitor.payment_completed' => 1,
'Competitor.weightgroup_id' => $current_matchsc['Matchschedule']['weightgroup_id'],
'Competitor.rank_id' => $current_matchsc['Matchschedule']['rank_id'],
'Competitor.degree_id' => $current_matchsc['Matchschedule']['degree_id'],
'Competitor.gender' => $current_matchsc['Matchschedule']['gender']
)
)
);
If is_black and is_adult are not always required for the relationship, you would want to move those conditions from the model and pass them in via the contain parameter of the find operation as needed.
这篇关于CakePHP多个JOIN findAll条件问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
