[Dbix-class] Re: has_many/belongs_to relationship to non-primarykey

[Octavian Rasnita]

Ok, I'm glad it works. Will this patch be included in the module?

I have also seen that the keys that are the base of the relations should be 
integers. Is it possible to use char or varchar instead?
I know that the speed is bigger when using integers, but sometimes it would 
be much easier to use char indexes.

Octavian

----- Original Message ----- 
From: "Matija Grabnar" <matija at serverflow.com>
To: <dbix-class at lists.rawmode.org>
Sent: Sunday, March 25, 2007 12:21 AM
Subject: Re: [Dbix-class] Re: has_many/belongs_to relationship to 
non-primarykey


> Octavian Rasnita wrote:
>> A key could force a column to have unique values, but those values could 
>> be null if I remember well, and if there are more null values, I don't 
>> know if that column can be used for a relationship...
> That's a valid concern, and initially I shared it. But it appears to 
> work - if the key value is null, a normal  join will not pick any values 
> from the has_many table.
>
> Let me demonstrate:
>
> CREATE TABLE `a` (
>  `i` integer(11) NOT NULL DEFAULT '0',
>  `j` integer(11) DEFAULT '0',
>  INDEX (`i`),
>  INDEX (`j`),
>  PRIMARY KEY (`i`),
>  UNIQUE `a_j` (`j`)
> ) Type=InnoDB;
>
> CREATE TABLE `b` (
>  `id` integer(11) NOT NULL DEFAULT '0',
>  `j` integer(11) DEFAULT '0',
>  INDEX (`id`),
>  INDEX (`j`),
>  PRIMARY KEY (`id`),
>  CONSTRAINT `b_fk_j` FOREIGN KEY (`j`) REFERENCES `a` (`j`) ON DELETE 
> CASCADE ON UPDATE CASCADE
> ) Type=InnoDB;
>
> select * from a;
> +---+------+
> | i | j    |
> +---+------+
> | 4 | NULL |
> | 1 |    1 |
> | 2 |    2 |
> | 3 |    3 |
> +---+------+
> select * from b;
> +----+------+
> | id | j    |
> +----+------+
> |  7 | NULL |
> |  8 | NULL |
> |  9 | NULL |
> |  1 |    1 |
> |  2 |    1 |
> |  3 |    1 |
> |  4 |    2 |
> |  5 |    3 |
> |  6 |    3 |
> +----+------+
>
> select * from a join b on (a.j=b.j);
> +---+------+----+------+
> | i | j    | id | j    |
> +---+------+----+------+
> | 1 |    1 |  1 |    1 |
> | 1 |    1 |  2 |    1 |
> | 1 |    1 |  3 |    1 |
> | 2 |    2 |  4 |    2 |
> | 3 |    3 |  5 |    3 |
> | 3 |    3 |  6 |    3 |
> +---+------+----+------+
>
> Of course, if we are interested in the NULL values of the key, we need to 
> use "left join"
> select * from a left join b on (a.j=b.j);
> +---+------+------+------+
> | i | j    | id   | j    |
> +---+------+------+------+
> | 4 | NULL | NULL | NULL |
> | 1 |    1 |    1 |    1 |
> | 1 |    1 |    2 |    1 |
> | 1 |    1 |    3 |    1 |
> | 2 |    2 |    4 |    2 |
> | 3 |    3 |    5 |    3 |
> | 3 |    3 |    6 |    3 |
> +---+------+------+------+
>
> Notice how even though several rows in b have NULL as key value, they 
> still do not appear
> in the result of the join. The relationship works.
>
> Note, (and I just checked this), if you declare that the key in a can be 
> NULL, and the key in b is declared NOT NULL, mysql still creates the 
> tables.
>
> I checked with postgresql, and the same is valid there (it too allows a 
> NOT NULL foreign key pointing to a UNIQUE key which can be NULL, and still 
> returns the expected results after a join).
>
> Best regards,
>                Matija Grabnar
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: 
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/