CODEWITHSUNDEEP
sqlcakephpcakephp-2.1
Sanganabasu
Sanganabasu

Reputation: 927

Convert sql statement to cakephp statment

I am using cakephp 2.1 and written sql statement as follows.

SELECT * FROM industry
LEFT JOIN movie ON movie.industry_id = industry.id
LEFT JOIN (
        SELECT MAX(id) AS TID ,movie_id FROM trailer
        GROUP BY movie_id

      ) AS c ON c.movie_id = movie.id
LIMIT 0, 4;

where industry is "Industry" model, movie is "Movie" model and trailer is "Trailer" model and I have tried this one.

$options['joins'] = array(
        array(
            'table' => 'movies',
            'alias' => 'Movie',
            'type' => 'left',
            'conditions' => array('Industry.id = Movie.industry_id')), 
        array(
            'table' => 'movie_trailers',
            'alias' => 'Trailer',
            'type' => 'left',
            'conditions' => array('Trailer.movie_id = Movie.id')));

    $trailers = $this->Industry->find('all', $options);

So please suggest me to convert this statement to cakephp statement.

Upvotes: 0

Views: 288

Answers (2)

Chuck Burgess
Chuck Burgess

Reputation: 11574

It's hard to tell what you are trying to accomplish with your query. But it looks like what you really want is the information about the four most recent trailers.

That being as it is, you can always just throw it all in the join:

$records = $this->Industry->find('all', array(
    'fields' => array('Trailer.id', 'Industry.id', 'Industry.Name', 'Movie.id', 'Movie.name'),
    'limit' => 4,
    'joins' => array(
        array(
            'table' => 'movies',
            'alias' => 'Movie',
            'type' => 'LEFT',
            'conditions' => array(
                'Movie.industry_id = Industry.id'
            ),
        ),
        array(
            'table' => '(SELECT MAX(id) As id, movie_id FROM trailers GROUP BY movie_id)',
            'alias' => 'Trailer',
            'type' => 'LEFT',
            'conditions' => array(
                'Trailer.movie_id = Movie.id'
            ),
        ),
    ),
));

It will defeat the portability of the ORM because of the query thrown in the table of the join, but it works.

Here is another question that may be of use: CakePHP and subquery

Upvotes: 1

dogmatic69
dogmatic69

Reputation: 7575

Your SQL is a join with a sub query, while the Cake code is join only.

You can do a sub query but I would look for a way to do it with a join only.

Given the lack of details in your question I cant help with a better query.

Upvotes: 1

Related Questions