CODEWITHSUNDEEP
sqlcakephp
Dyary
Dyary

Reputation: 810

Converting SQL query to CakePHP

I have this SQL query that I need to convert to CakePHP. I used this website [http://dogmatic69.com/sql-to-cakephp-find-converter][1] that converts the code but I doesn't work in the way that I want. There is no results.

I think it is because it creates an empty array here is the SQL code : 

SELECT shops.phone1 FROM galleries , albums , shops
WHERE  galleries.album_id = albums.id and albums.shop_id = shops.id and galleries.id = 210

and this is the result the website gives me : 

$options = array(
    'fields' => array(
        'shops.phone1',
    ),
    'joins' => array(

        array(
        ),
    ),
    'conditions' => array(
        'Gallery.album_id = albums.id',
        'albums.shop_id = shops.id',
        'Gallery.id' => '210',
    ),
);
$data = $this->find('all', $options);

but the code doesn't work.

Any Ideas what could be wrong ?

Thanks

Upvotes: 0

Views: 263

Answers (1)

Sehdev
Sehdev

Reputation: 5682

There are many ways to achieve this. If you have defined your associations correctly then you can do this:

$data = $this->Shops->find('all')
             ->contain(['Galleries','Albums'])
             ->fields(['Shops.phone1'])
             ->where(['Galleries.id' => 210]);

Otherwise you can use custom join to generate your query:

$data = $this->Shops->find('all') 
                     ->join([
                           'albums' => [
                                 'table' => 'albums',
                                 'type' => 'INNER', // define your join type here
                                 'conditions' => 'albums.shop_id = Shops.id',
                                  ],
                           'galleries' => [
                                  'table' => 'galleries',
                                  'type' => 'INNER', // define your join type here
                                  'conditions' => 'galleries.album_id=albums.id',
                             ]
                     ])
                   ->select(['Shops.phone1'])
                   ->where(['galleries.id' => 210]);

Further Reading: Cakephp -> Query Builder -> Adding Joins

Upvotes: 1

Related Questions