converting simple query to cake query ?

Question

Hi , actually i have 1 query but i am unable to convert it into cakephp query format.

$result = "select * from esl_userresults,esl_lyrics where esl_userresults.esl_songID = esl_lyrics.id and esl_lyrics.song_name like '%".$esl_keyword."%'" ;

Please help

thanks

Answer 1

You can easily run direct sql queries on cake using e.g.: $this->Picture->query("SELECT * FROM pictures LIMIT 2;"); (cake manual)

Or try something simillar to this:

    $result = Esl_Userresults->find('all' , array('conditions' => array(
             "Esl_Userresults.esl_songID" => "Esl_Lyrics.id",
             "Esl_Lyrics.song_name LIKE" => '%'.$esl_keyword.'%'),
              'recursive' => 1)
              );

..given that you have properly linked Esl_Userresults and Esl_Lyrics models.

Answer 2

Use Containable behaviour rather than recursive. It will give you control down to individual field level. Using it now, at an early stage, will make it second nature later.

If you get confused building the conditions, build it outside the method call.

Try to avoid the use of double quotes except where you need to include escaped or parsed data - they're slower.

$conditions = array(
    'EslUserresult.esl_songID' => 'EslLyric.id',
    'EslLyric.song_name LIKE' => '%'.$esl_keyword.'%'
                   )
$this->EslUserresult->contain('EslLyric.text');
$result = $this->EslUserresult->find('all',array('conditions'=>$conditions));