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+unsubscribe@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.