CakePHP get record from another table if the id matches

Question

I am new to CakePHP and still learning as I make my way through each problem.

I have two tables: customers and stores. In stores table I have a foreign key called customer_id that holders customer id from customers table.

In CakePHP, I created controllers, models, and view for above tables. From CustomerController.php in view action, I am trying to get store that matches id of customer.

CustomerController.php page:

class CustomersController extends AppController
{

    public function index()
    {
        $customers = $this->Customers->find('all'); // Find all the records from the database.
        $this->set('customers', $customers);
        $stores = $this->Customers->Stores->find('all');
        $this->set('stores', $stores);        
    }

    public function view($id = NULL) 
    {
        $customer = $this->Customers->get($id); // Find a record for individual record.
        $this->set('customer', $customer);

        // $stores = $this->Customers->Stores->find('all');
        $stores = $this->Customers->Stores->find('list', [
                'keyField' => 'id',
                'valueField' => 'store_name'
            ]);        
        $this->set('store', $stores);
    }
}

SQL tables:

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `first_name`, `last_name`) VALUES
(1, 'Ray', 'Mak'),
(2, 'John', 'Smith'),
(3, 'Mike', 'Gorge');

-- --------------------------------------------------------

--
-- Table structure for table `states`
--

CREATE TABLE IF NOT EXISTS `states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `states`
--

INSERT INTO `states` (`id`, `state_name`) VALUES
(1, 'TX'),
(2, 'VA'),
(3, 'WI'),
(4, 'AZ'),
(5, 'FL');

-- --------------------------------------------------------

--
-- Table structure for table `stores`
--

CREATE TABLE IF NOT EXISTS `stores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `store_name` varchar(50) DEFAULT NULL,
  `corp_name` varchar(200) DEFAULT NULL,
  `street_address` varchar(200) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `zipcode` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customers_idx` (`customer_id`),
  KEY `states_idx` (`state_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

View page:

<pre>
<?php 
print_r(json_encode($store));
print_r(h($customer));
?>
</pre>

I can use a custom SQL query with left join to get the results, but in cakephp its confusing how I can get data from another table when id matches.

Any help will be appreciated :)

I think I solved my own problem. I was able to use conditions which took me a while to understand because the document on cakephp is not very clear when explaining things. $stores = $this->Customers->Stores->find('all',[ 'conditions' => array('Stores.id' => $id)]); — Ray
And read the manual section on Associations. If you set up your models correctly (which would be done for you automatically if you use the Bake tool), your query in the customer view method should simply be $this->Customers->get($id, ['contain' => ['Stores']]); — Greg Schmidt
Hi Greg, thank you so much for your solution and it works great. :) However, I am still confuse when to use contain. If I understand it correctly, your solution will get all the data that matches id from Stores model table col customer_id. — Ray

MotsManish MotsManish · Accepted Answer · 2015-09-09T06:04:16

Ray,

You have mentioned in comments that you resolved your issue, but if you are using the below code in CustomersController :: view function, then $id does not represent stores id.

$stores = $this->Customers->Stores->find('all',[ 'conditions' => array('Stores.id' => $id)]);

In order to get stores associated with customers, you must refer to below code

$stores = $this->Customers->Stores->findByCustomerId($id);
//where $id represents customer_id from stores table

CakePHP get record from another table if the id matches

1 Answers