Choosing field names while finding results from two tables

I have two tables (which happen to be in two different databases). "clients" and "domains", clients can have multiple domains.

This is the code i am using:

$this->Domain->find('all', array(
    'order' => 'domain ASC',
    'fields' => array(
        'Domain.id',
        'Domain.domain',
        'Server.name',
        'Client.id',
        'Client.name'
    )
));

When i return all the fields by not using the 'fields' => array() everything works fine, as soon as i ask for specific fields, it says:

SQL Error: 1054: Unknown column 'Client.id' in 'field list'

Everything also works fine if i just remove the two Client columns (The Client model is the only model which is on another database.

If clients hasMany domains, So your models should be called like $this->Domain->find('all'); explicitly passing fields Client.id will show error, as its not the part of domains table, enable sql dumping using debug=2 and see how queries are being run.

You models should be

// in client.php model - having structure - id, name
$hasMany = 'Domain';

// in domain.php model - having structure - id, name, client_id
$belongsTo = 'Client';

This should work like this

$this->Domain->recursive = 1;
$data = $this->Domain->find('all');
// $data = Array ( 'Domain' => ********, 'Client' => ****** )

If your two tables are in different databases, you're really making your life difficult. AFAIK, Cake doesn't support joining two tables (or enforcing relationships) between two different databases. Why do you have the client table in a separate db?

If you can't move your table, I think you're going to have to write some custom code inside your Domain model, so that it will use the (default) db connection string for domains, but will instantiate and connect another db resource to the other database. See http://bakery.cakephp.org/articles/doze/2010/03/12/use-multiple-databases-in-one-app-based-on-requested-url for how to do it -- skip ahead to the section entitled, "Select Correct Database Dynamically".

HTH, Travis