Magento – select row based on foreign key

database

How do you select a row based on that tables foreign key? My table and primary key is set and works as expected but now I need data from table to based on that foreign key. I have managed to get it to work by doing this

    $params = $this->getRequest()->getParam('id');
    $resource = new Mage_Core_Model_Resource();
    $read = $resource->getConnection('core_read');
    $select = $read->select() 
                   ->from('prefcentre_options') 
                   ->where('prefCentre_id = ?', $params);
    $user = $read->fetchRow($select);

But I am sure there is a better way, I have tried (but did not work)

    $params = $this->getRequest()->getParams();
    $user = Mage::getModel('prefs/prefsemail');
    $user->addFieldToFilter('prefcentre_id');
    $user->load($params);

===EDIT===

Sorry I should have been more specific (I was trying to keep my question concise and consequently neglected required information)

I have pulled data from table one based on the primary key, but using that same number I need to pull data from table two, which uses that same number as its foreign key, but obviously the primary key of table two does not correspond with the foreign key and so far I have been getting the data from the row that corresponds with the primary key

Best Answer

If your model extends Mage_Core_Model_Abstract and your resource model is configured correctly you should be able to load the row like this:

$model = Mage::getModel('prefs/prefsemail')->load($id);

Then all your columns should be found in $model->getData()

EDIT
If you want to get data from an other table using a FK try this:

$collection = Mage::getModel('prefs/prefsemail')->getCollection()->addFieldToFilter('fk_name', $fkValue);
foreach ($collection as $item){
    //do something with $item
}
Related Topic