Cigo Zarko Fri Aug 06 17:45:19 -0400 2010

Subject: Many-to-many relationship


i'm having a problem with many to many relationship.

This is the DB schema:
And these are the models:

The problem is that Transaction::first() doesn't return any tags. I've tried all sorts of combinations in the $has_many property of the Transaction model, but nothing seems to work. What am I missing here?

Thanks for help and keep up the good work ;)

Jacques Fuentes Sun Aug 08 12:30:09 -0400 2010

I can't be sure off the top of my head, but you may have a case sensitivity problem.

Does the following work?

1 Transaction::first()->transactiontag

Cigo Zarko Mon Aug 09 02:49:51 -0400 2010

Nope, nothing is returned. If i use Transaction::first()->TransactionTag, then an Undefined property exception is raised.
Doing a little debugging, i found out that only the following SQL query is executed:
SELECT * FROM `Transaction` LIMIT 0,1

Cigo Zarko Mon Aug 09 17:13:04 -0400 2010

OK, the problem lies in the load function of HasMany class. The constructed SQL query contains an invalid column tag_id:

SELECT `Tag`.* FROM `Tag` INNER JOIN `TransactionTag` ON(`Tag`.TagID = `TransactionTag`.tag_id) WHERE `transactionid`=?

It seems that everytime tag_id column is constructed from class name instead from given foreign key. This is done on line 478 in the HasMany class. The line is as follows:
$this->set_keys($this->get_table()->class->getName(), true);

and the set_keys function is like this:

if (!$this->foreign_key || $override)
$this->foreign_key = array($this->keyify($model_class_name));
if (!$this->primary_key || $override)
$this->primary_key = Table::load($model_class_name)->pk;

The $override parameter is true and it overrides the TagID foreign key set manually. Is there any workaround?

Jacques Fuentes Sat Aug 14 13:37:25 -0400 2010

First off, please be sure you are using version 1.0 of php-ar (downloadable here). Next, there is a problem with your $primary_key declaration for TransactionTag class.

class TransactionTag extends ActiveRecord\Model
    static $table_name = 'TransactionTag';
    static $primary_key = array('TransactionID', 'TagID'); <= This is WRONG.

PHP-AR does not accept composite primary keys. You can only define a single primary key and that value needs to be a string -- not an array.

Cigo Zarko Wed Aug 18 12:13:03 -0400 2010

Ah, so this is it. Thanks Jacques for investigating.