Michiel van Leening Wed Mar 02 17:21:30 -0500 2011

Subject: Creating has_many relationship with non-standard foreign_key names

Hi,

I've been trying out phpactiverecord over other ORM systems since i really like the clean approach and the well written docs. However, as soon as I deviated from standard practices things went awry :-)

I cannot get a many-to-many relation to work with a non-standard foreign_key name. I have a couple of tables which are historically not in english (they're in Dutch). I would however like to start using english classnames (but i cannot rename my tables and/or column names or things will break).

Given the following (mysql) table layout: http://pastie.org/1626332 and the following php code http://pastie.org/1626335 I am able to get at the related entries in both the House and the Keyword objects through the Housekeyword object.

$house = House::find('first');
var_dump($house->keywords);

yields the desired results.

If I however change the sql so that the column linking from Housekeyword to House is renamed to 'huis_id' and the foreign_key in both House and Housekeyword is updated to match this, I notice that the column to join on is being overwritten by 'house_id'. It somehow overwrites this in Relationship.php, although debugging shows that the correct values are passed into the HasMany class.

Using $has_and_belongs_to_many resulted in an immediate crash, and i've not bothered to venture down that path :-)

I've tried this with both the 1.0 as the git version (https://github.com/kla/php-activerecord)

I really hope anyone can shed some light on this, since it might mean i have to switch to Doctrine2, which I think is a bit too heavy for my needs.

Edit: i am now able to get at the keywords from the House object by specifying:

array('keywords', 'through' => 'housekeywords', 'foreign_key' => 'huis_id')

However, doing this from the Keyword object:

array('houses', 'through' => 'housekeywords', 'foreign_key' => 'keyword_id')

still doesn't work and produces the following SQL:

SELECT `huizen`.* FROM `huizen` INNER JOIN `huis_tref` ON(`huizen`.nummer = `huis_tref`.house_id) WHERE `keyword_id`=?

Devi Mandiri Thu Mar 03 09:12:12 -0500 2011

Naming in PHP-AR is tricky, hope this give you some light: http://pastie.org/1628683

edit: forgot the sql schema http://pastie.org/1628705

Michiel van Leening Thu Mar 03 11:05:45 -0500 2011

Hi,

Thanks for your effort. You seem to have missed my point: i cannot rename the database. This will break things:

but i cannot rename my tables and/or column names or things will break

Getting it to work using the default <object>_id mapping was no problem at all :-)

I do hope one of the developers is reading this, as i can imagine i will not be the only person who cannot rename his database.

I even considered using a view, but it's less of a hassle just to start using Doctrine2

Devi Mandiri Thu Mar 03 11:11:29 -0500 2011

Assume you're using MySQL.
I can not execute this with phpmyadmin:

CREATE TABLE `huis_tref` (
  `house_id` int(11) NOT NULL,
  `keyword_id` int(11) NOT NULL,
  KEY `huis_id` (`huis_id`),
  KEY `keyword_id` (`keyword_id`)
)

It produce error: Key column 'huis_id' doesn't exist in table
So, I've changed the sql chema to test your problem.

Michiel van Leening Thu Mar 03 11:40:05 -0500 2011

That might be because you have to write:

CREATE TABLE `huis_tref` (
`huis_id` int(11) NOT NULL,
`keyword_id` int(11) NOT NULL,
KEY `huis_id` (`huis_id`),
KEY `keyword_id` (`keyword_id`)
)

Notice the name of the first column.

Devi Mandiri Thu Mar 03 11:43:47 -0500 2011

My fault, I was just copy and paste sql schema that you've provided.
So, can you show me your current working sql with those 3 tables(huizen, trefwoorden, huis_tref) ?

Michiel van Leening Thu Mar 03 11:54:31 -0500 2011

No, my fault, the original pastie was incorrect. Below is the sql with which it works (only the join-table, the others are correct):

CREATE TABLE `huis_tref` (
`house_id` int(11) NOT NULL,
`keyword_id` int(11) NOT NULL,
KEY `house_id` (`house_id`),
KEY `keyword_id` (`keyword_id`)
)

Below is the sql with which it doesn't work:

CREATE TABLE `huis_tref` (
`huis_id` int(11) NOT NULL,
`keyword_id` int(11) NOT NULL,
KEY `huis_id` (`huis_id`),
KEY `keyword_id` (`keyword_id`)
)

Using this sql PHP-AR still tries to use house_id when referencing Keyword->houses

Devi Mandiri Thu Mar 03 12:05:12 -0500 2011
Michiel van Leening Thu Mar 03 12:16:37 -0500 2011

Thanks, i'm not at home now so can't access the code, but have you also tried it the other way around?

$keyword = Keyword::find('first');        
var_dump($keyword->houses);
Devi Mandiri Thu Mar 03 12:18:50 -0500 2011

Not yet, I'll let you know.

Devi Mandiri Thu Mar 03 13:56:34 -0500 2011

Try this http://pastie.org/1629660

Note: tested under WinXP

Michiel van Leening Thu Mar 03 15:47:41 -0500 2011

Wow, this soo works! Totally awesome!

I would've never figured out to use an array as the value for the through key.

Thanks a ton! It works perfectly on Ubuntu Linux ;-)

Devi Mandiri Thu Mar 03 18:59:05 -0500 2011

I believe that's what's forum for ;)

Michiel van Leening Mon Mar 07 08:30:58 -0500 2011

Hi,

When i tried to rename one of the columns from the joining table, it wouldn't work anymore, since it was looking again for the <class>_id column.

The modified sql is here : http://pastie.org/1643143
I renamed 'keyword_id' to 'tref_id'

Tried several approaches, but the query keeps coming up as

SELECT `trefwoorden`.* FROM `trefwoorden` INNER JOIN `huis_tref` ON(`trefwoorden`.id = `huis_tref`.keyword_id) WHERE `huizen_nummer`=?
Devi Mandiri Mon Mar 07 14:52:27 -0500 2011

I'm affraid I can't help you this time. I think this is a HasAndBelongsToMany (Relationship.php line 556) and it it's not finish yet (Relationship.php line 552).

For now, this is the only think pop up my mind:

$join = 'INNER JOIN `huis_tref` ON(`trefwoorden`.id = `huis_tref`.tref_id)';
$house = Keyword::all(array(
    'joins' => $join,
    'conditions' => 'huis_id=1'
));
print_r($house);

Michiel van Leening Mon Mar 07 15:53:22 -0500 2011

I gathered as much. I wish i could contribute the HasAndBelongsToMany class, but alas, my knowledge doesn't reach that far.

Your suggestion for a join is elegant and easily implementable. Thank you.

Cristhian Boujon Wed Oct 24 09:44:34 -0400 2012

Hello all,

Can someone help me with my thread?
http://www.phpactiverecord.org/boards/4/topics/1658-has_many-does-not-work
It is similar to this.

Thank you!

(1-16/16)