Jeroen le Roy Mon Oct 17 10:28:30 -0400 2011

Subject: Problem with relationships between databases

Hi there! Currently i'm messing about with a quite difficult relationship between 2 tables in different databases with a 'through' which I can't seem to fix this is the current situation:

  • Client product Database: Contains 10.000 or so products that is updated daily and used between some applications, a database I can't change based on MYSQL used by several other products
  • Application database: The database I created for create 'stuff' based on a list of the above products.

The application has it's own category structure, and for those categories the customer can assign products from the other database. I've managed to place a relationship between the user selected products (CategoryProducts) to the external database (ExternalProducts) and have a relationship between the category (Category) and the products selected by the user (CategoryProducts). But I can't seem to get the products from the external database (ExternalProducts) directory through the category. This is what I have:

class Category extends ActiveRecord\Model {

static $has_many = array(
array( 'categories' ),
array( 'category_products', 'class_name' => 'CategoryProduct' ),
array( 'external_products', 'connection' => 'default', 'through' => 'category_product', 'class_name' => 'ExternalProduct' )
);
}

class CategoryProduct extends ActiveRecord\Model {

static $connection        = 'default';
static $belongs_to = array(
array( 'category')
);
static $has_one = array(
array( 'external_product', 'class_name' => 'ExternalProduct', 'foreign_key' => 'id')
);
}

class ExternalProduct extends ActiveRecord\Model {

static $connection        = 'products';
static $table_name    = 'products';
static $belongs_to    = array(
array( 'offer_product' ),
array( 'category', 'class_name' => 'ExternalCategory', 'foreign_key' => 'category_id' ),
array( 'category_product', 'class_name' => 'CategoryProduct', 'foreign_key' => 'external_product_id')
);
}

After many wrong attempts the above classes gives me the follow error:

Message: 42S02, 1146, Table 'external_products.category_products' doesn't exist. Which is true since the table is in not in the external database but in the applications database.

I tried forcing the connection of CategoryProduct to the applications database (as you can see above) but that doesn't seem to change it, I also tried adding 'connection' => 'default' to the relationship.

The following does work:

Category::find(2)->category_products
CategoryProduct::find(2)->external_product
Category::find(2)->category_products0->external_product

but
Category::find(2)->external_products gives me the earlier mentioned error.

(i'm sorry, but the markup seems to messed up while viewing if wanted I could fix this if I know how)