Re: multi table query with cross reference table
OK - NVM
I figured it out
what I learned - how to create a query / subquery object expression to use
> //create subquery
> $sub = $query->newExpr('select t.op_team_name from Teams t
> where (t.ws_team_name=Matches.team_home OR
> t.ws_team_name=Matches.team_away) and
> (t.op_team_name=OpMatches.team_home OR
> t.op_team_name=OpMatches.team_away)');
>
> $query->where(['Matches.odds_url is null'])
> ->contain(['OpMatches' => [
> 'foreignKey' => false, //turn off foreignKey when doing
> initial matching - data is not matched yet
> 'queryBuilder' => function($q){
> return $q->where([
> 'Matches.match_date = OpMatches.match_date',
> ]);
> }//end - querybuilder
> ],
> //'MatchesCsv',
> 'Seasons' //load the season object too - we need the
> league id
> ])->andWhere(function ($exp) use($sub) {
> return $exp->in("(Matches.team_home OR Matches.team_away)"
> , $sub);
> });
>
> return $query;
>
>
>
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to cake-php+unsubscr...@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Re: multi table query with cross reference table
OK, i found the right query - now i just need to modify it into cakephp orm SELECT Matches Seasons... OpMatches... FROM matches Matches LEFT JOIN seasons Seasons ON (Seasons.id = (Matches.season_id) AND Seasons.stage_id = (Matches.stage_id)) LEFT JOIN op_matches OpMatches ON (Matches.match_date = OpMatches.match_date) /* AND (Matches.team_home = t.op_team_name OR Matches.team_home = OpMatches.team_home OR Matches.team_away = t.op_team_name OR Matches.team_away = OpMatches.team_away)) */ WHERE Matches.odds_url is null AND( ((Matches.team_away OR Matches.team_home) IN (select t.op_team_name from Teams t where (t.ws_team_name=Matches.team_home OR t.ws_team_name=Matches.team_away) and (t.op_team_name=OpMatches.team_home OR t.op_team_name=OpMatches.team_away) ) ) /*OR Matches.team_away IN (select t.op_team_name from Teams t where t.ws_team_name=Matches.team_away and t.op_team_name=OpMatches.team_away) )*/ ); You'll notice - the 'AND' part of the query is in parens - how do I get cakephp to do that? -- Like Us on FaceBook https://www.facebook.com/CakePHP Find us on Twitter http://twitter.com/CakePHP --- You received this message because you are subscribed to the Google Groups "CakePHP" group. To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscr...@googlegroups.com. To post to this group, send email to cake-php@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php. For more options, visit https://groups.google.com/d/optout.
multi table query with cross reference table
The problem,
I have two tables that need to be 'matched up' from data in a third table
I am currently using a contains query - but I think the final part should
be a subquery - just not sure of how to go about it.
I dont need any 'data' from the third table - just using it to compare some
name (string field)
Any suggestions would be awesome...
SELECT Matches
Seasons...
OpMatches...
FROM matches Matches
LEFT JOIN seasons Seasons ON (Seasons.id = (Matches.season_id) AND
Seasons.stage_id = (Matches.stage_id))
LEFT JOIN op_matches OpMatches ON (Matches.match_date =
OpMatches.match_date) /* AND
(Matches.team_home = t.op_team_name OR Matches.team_home =
OpMatches.team_home OR Matches.team_away = t.op_team_name OR
Matches.team_away = OpMatches.team_away)) */
WHERE Matches.odds_url is null
AND(
(Matches.team_home = OpMatches.team_home OR Matches.team_away =
OpMatches.team_away)
/*need the other stuff to compare team names - see the query below
OR (Matches.team_home IN (select t.op_team_name from Teams t where
t.ws_team_name=Matches.team_home and t.op_team_name=OpMatches.team_home) )
OR (Matches.team_away IN (select t.op_team_name from Teams t where
t.ws_team_name=Matches.team_away and t.op_team_name=OpMatches.team_away) )
*/
)
/* the query im using now */
$query->where(['Matches.odds_url is null'])
->contain(['OpMatches' => [
'foreignKey' => false, //turn off foreignKey when doing
initial matching - data is not matched yet
'queryBuilder' => function($q){
return $q->where([
'Matches.match_date = OpMatches.match_date',
])
->andWhere(function($exp){
return $exp->or_([
'Matches.team_home = t.op_team_name' ,
'Matches.team_home = OpMatches.team_home' ,
'Matches.team_away = t.op_team_name' ,
'Matches.team_away = OpMatches.team_away']);
});
}//end - querybuilder
],
//'MatchesCsv', //this gets turned on later - not
needed now
'Seasons' //load the season object too - we need the
league id
])
->join([//not sure a join is the right to go - i dont need
the data - just using columns to compare string fields
'table' => 'teams',
'alias' => 't',
'type' => 'LEFT',
'conditions' => ['t.league_id' => 'Seasons.league_id' ,
'(t.ws_team_name = Matches.team_home OR t.ws_team_name
= Matches.team_away)']
]);
return $query;
I apologize if the problem isn't clear.
I can try to explain more in depth if anyone needs.
Thanks,
Joe
p.s.
i will set a .001 btc bounty for solution
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to cake-php+unsubscr...@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
