Converting SQL query to CakePHP

Question

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

Cakephp version?

Sehdev
– Sehdev

2018-10-21 14:38:34 +00:00
Commented Oct 21, 2018 at 14:38 — Sehdev
– Sehdev, Commented Oct 21, 2018 at 14:38
Cakelphp version 3.6

Dyary
– Dyary

2018-10-21 15:05:28 +00:00
Commented Oct 21, 2018 at 15:05 — Dyary
– Dyary, Commented Oct 21, 2018 at 15:05

Sehdev · Accepted Answer · 2018-10-22 16:31:03Z

1

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

edited Oct 22, 2018 at 16:31

answered Oct 21, 2018 at 15:38

Sehdev

5,7123 gold badges14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dyary Over a year ago

Thank you so much @Greg Schmidt. Problem Solved

Greg Schmidt Over a year ago

@sehdev solved it, I just edited in a couple of missing quotation marks.

Collectives™ on Stack Overflow

Converting SQL query to CakePHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related