| Subject: | there are unexpected JOIN instead of LEFT JOIN for 'has_many' relationship |
I have three table in next relationship:
package SafeVPN::DB::Result::Locality;
__PACKAGE__->has_many( servers => 'SafeVPN::DB::Result::Server', 'locality_id', {cascade_delete => 0});
__PACKAGE__->has_many( addresses_view => 'SafeVPN::DB::Result::Pool::Address_view', 'locality_id', {cascade_delete => 0});
package SafeVPN::DB::Result::Server;
__PACKAGE__->has_many('addresses_view', 'SafeVPN::DB::Result::Pool::Address_view', 'server_id', {cascade_delete => 0});
__PACKAGE__->belongs_to('locality', 'SafeVPN::DB::Result::Locality', 'locality_id', {cascade_delete => 0});
package SafeVPN::DB::Result::Pool::Address_view;
__PACKAGE__->belongs_to( server => 'SafeVPN::DB::Result::Server', 'server_id' );
__PACKAGE__->belongs_to( locality => 'SafeVPN::DB::Result::Locality', 'locality_id');
__PACKAGE__->belongs_to( subnet => 'SafeVPN::DB::Result::Pool::Subnet', 'subnet_id' );
in Locality.pm running
this: $self->search_related( 'servers')->search_related('addresses_view')->as_query
or this: $self->servers->search_related('addresses_view')->as_query
generates next query:
SELECT "addresses_view"."id", "addresses_view"."subnet_id", "addresses_view"."ip", "addresses_view"."usage", "addresses_view"."notes", "addresses_view"."locality_id", "addresses_view"."server_id"
FROM "servers" "me"
JOIN "pool_addresses_view" "addresses_view" ON "addresses_view"."server_id" = "me"."id"
WHERE ( "me"."locality_id" = ? )
But relying on 'has_many' relationship (doc says: "This relationship refers to zero or more records in the foreign table (e.g. a LEFT JOIN)") I expect 'LEFT JOIN' but, as you see, I get 'JOIN'
So if 'server' from 'locality_id' has no ip addresses I loose that server from results