Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 104300
Status: open
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: kes-kes [...] yandex.ru
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in: (no value)
Fixed in: (no value)



Subject: there are unexpected JOIN instead of LEFT JOIN for 'has_many' relationship
Download (untitled) / with headers
text/plain 1.6k
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
From: kes-kes [...] yandex.ru
Download (untitled) / with headers
text/plain 380b
**UPDATE** adding attribute **join_type => 'left'** like: __PACKAGE__->has_many( addresses_view => 'SafeVPN::DB::Result::Pool::Address_view', 'locality_id', {cascade_delete => 0,join_type => 'left'}); __PACKAGE__->has_many('addresses_view', 'SafeVPN::DB::Result::Pool::Address_view', 'server_id', {cascade_delete => 0,join_type => 'left'}); has no effect. Maybe BUG?
Download (untitled) / with headers
text/plain 603b
You are getting confused because you are thinking in SQL, not in set traversal. Show quoted text
> $self->servers->search_related('addresses_view')
This means "Get me the set of addresses_view, which are related to servers". The moment you called search_related() you "moved your context window" and $self->servers is completely gone on a logical level. In this context the *implementation detail* of JOIN is what ensures correct behavior (you don't get weird NULL-objects left over for servers that happen to have no address_view's) Does this make sense? If not let me know and I will try to explain it differently.
From: kes-kes [...] yandex.ru
Download (untitled) / with headers
text/plain 866b
As documented here: http://search.cpan.org/~ribasushi/DBIx-Class-0.082820/lib/DBIx/Class/Relationship/Base.pm#condition For example given: My::Schema::Author->has_many( books => 'My::Schema::Book', { 'foreign.author_id' => 'self.id' } ); A query like: $author_rs->search_related('books')->next will result in the following JOIN clause: ... FROM author me LEFT JOIN book books ON books.author_id = me.id ... Result in DOC is 'LEFT JOIN' so I expect LEFT JOIN, but I get 'INNER JOIN' instead. Must you FIX DOC? Please can you tell me right way to get select with LEFT JOIN while I am in Locality.pm (Relation definition file). Is this right? $self->result_source->schema->resultset('Locality')->search( undef, { join => { servers => 'addresses_view' } } )->as_query; If yes. is there a way to tell DBIx do not forget current 'locality.id'?
From: kes-kes [...] yandex.ru
I need all servers in current locality. Despite on it may have no ip addresses.
Subject: Re: [rt.cpan.org #104300] there are unexpected JOIN instead of LEFT JOIN for 'has_many' relationship
Date: Fri, 08 May 2015 15:27:06 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 764b
On 05/08/2015 03:24 PM, Eugen Konkov via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=104300 > > > As documented here: > http://search.cpan.org/~ribasushi/DBIx-Class-0.082820/lib/DBIx/Class/Relationship/Base.pm#condition > > > > For example given: > > My::Schema::Author->has_many( > books => 'My::Schema::Book', > { 'foreign.author_id' => 'self.id' } > ); > > A query like: > > $author_rs->search_related('books')->next > > will result in the following JOIN clause: > > ... FROM author me LEFT JOIN book books ON books.author_id = me.id ... > > > Result in DOC is 'LEFT JOIN' so I expect LEFT JOIN, but I get 'INNER JOIN' instead. Must you FIX DOC?
Yes, the documentation is incorrect.
Subject: Re: [rt.cpan.org #104300] there are unexpected JOIN instead of LEFT JOIN for 'has_many' relationship
Date: Fri, 08 May 2015 15:29:43 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 509b
On 05/08/2015 03:26 PM, Eugen Konkov via RT wrote: Show quoted text
> Is this right? > $self->result_source->schema->resultset('Locality')->search( undef, { join => { servers => 'addresses_view' } } )->as_query; > > I need all servers in current locality. Despite on it may have no ip addresses.
If it doesn't matter whether there is an address_view or not, why are you joining to addresses_view in the first place? Please explain the larger picture of what you are trying to do, so I can answer your question correctly.
From: kes-kes [...] yandex.ru
Download (untitled) / with headers
text/plain 177b
I need also info from server.*. Doing just $self->addresses_view has only 'server_id' field. It will be great if you allow to do: $self->servers->addresses_view->search( ... )
Subject: Re: [rt.cpan.org #104300] there are unexpected JOIN instead of LEFT JOIN for 'has_many' relationship
Date: Fri, 08 May 2015 16:08:46 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 686b
On 05/08/2015 04:03 PM, Eugen Konkov via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=104300 > > > I need also info from server.*. Doing just $self->addresses_view has only 'server_id' field. > > It will be great if you allow to do: > > $self->servers->addresses_view->search( ... )
You are still thinking in terms of SQL you want to execute. Stop, this is a distraction. A proper answer is: "I want to get all server objects + all the related addresses_view objects" Perhaps "in one query" can be an extra part of your requirement. Think in "sets" and tell me what you want to achieve *logically*. Ignore the SQL for a moment.
From: kes-kes [...] yandex.ru
I need all servers in current locality. And all server ips if it has them. in one query
Subject: Re: [rt.cpan.org #104300] there are unexpected JOIN instead of LEFT JOIN for 'has_many' relationship
Date: Fri, 08 May 2015 16:18:20 +0200
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 533b
On 05/08/2015 04:16 PM, Eugen Konkov via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=104300 > > > I need all servers in current locality. > And all server ips if it has them. > > in one query
https://metacpan.org/pod/DBIx::Class::ResultSet#prefetch I will close this ticket, as this is a basic API question, not a bug report. For lower latency and lower bandwidth answers consider perusing the big-red-button "Chat with us" on the left side of the page I just linked you to.
From: kes-kes [...] yandex.ru
Download (untitled) / with headers
text/plain 172b
I resolve my problem, by using $self->servers->search( undef, { join => 'addresses_view' } ); instaed of: $self->servers->search_related('addresses_view' ); thank you
From: kes-kes [...] yandex.ru
Download (untitled) / with headers
text/plain 164b
Questions that I have after reading doc/problems with doc cause this fail bug report I post here: https://rt.cpan.org/Public/Bug/Display.html?id=104289#txn-1494721


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.