[Dbix-class] $row->find_or_create_related does not find existing rows when $row is a new result

[Peter Rabbitson]

Robert Webb wrote:
> Hi there,
> 
>  
> 
> We're currently upgrading our DBIC from v0.08012 to the latest version
> (v0.08120) on a project I'm working on.  Most things are going smoothly,
> but we've run into a use case for find_or_create_related that used to
> work in the older DBIC but no longer does.  We had some code that was
> calling $row->find_or_create_related('rel', { ... }) on a new row object
> that hasn't been inserted into the database yet.  This ought to work
> because the relationship is a belongs_to, so it doesn't need to know
> anything about $row.  In the new version, the related row is always
> created regardless of whether the related object already exists in the DB.
> 
>  
> 
> We can change the code easily to just call find_or_create() on the
> related table itself, but we were doing it this way because we wanted to
> find_or_create on the related table without having to know what class to
> search on (that's my guess, anyway - the original impetus for the way
> this was written is long gone now).  Having the new row object and the
> relationship names was an easy way to do this, and seems like a quirky
> yet valid use case, so I would argue that it ought to be supported.  Or
> at least it would be nice if there was some kind of error or warning or
> something, rather than the current behavior which is to silently create
> a new row in the related table even if the find() would have succeeded.
> 
>  <snipped>
> 
> We have some code that does a find_or_create_related call like this:
> 
>  
> 
>     $build_datum =
> $schema->resultset("NBT::Schema::Icebox::BuildData")->new_result({});
> 
>     # actually we loop through a list of relationships next, but I'm
> keeping it simple for the sake of the example
> 
>     $build_datum->find_or_create_related("build_url", { name => '...' });
> 
>  
> 
> This produces some strange SQL that looks like this:
> 
>  
> 
>     SELECT me.id, me.name, me.modified_ts FROM build_urls me WHERE ( (
> me.name = ? AND 1 = 0 ) ): '...'
> 
>  
> 
> The 'AND 1 = 0' condition makes the select impossible, so a related row
> is always created.  The impossible condition seems to come from
> DBIx::Class::ResultSource->_resolve_condition(), which decides that the
> relationship condition is unresolvable and returns \'1 = 0'.  The
> condition is 'unresolvable' because the build_url column is not
> in_storage on $build_datum, because $build_datum is a new_result.  If
> the 'AND 1 = 0' were left out, this would do the right thing, I think. 
> But maybe theres a good reason for its existence in this case and I'm
> just confused?
> 

Let's consider what happens if we "fix" it. You created a CD, which has
an artist_id FK, which is obviously unfilled. You find_or_create an Artist
on this uninserted object, essentially searching for an Artist by name (we
assume that 1 = 0 is omitted). An artist matching the (hopefully unique)
name is returned. What happens next? The CD is still not linked to the
artist in any way, as there is nothing to adjust the FK column artist_id.
So you found some Artist that (as far as DBIC is concerned) is not related
in any way to the CD on which the method was called.

In fact even if no artist was found, all that create_related would do is
populate the Artist table, without actually setting the CD artist_id FK
to something sensible. This in itself may or may not be a bug, I am not
sure.

Unfortunately this is some gray area stuff that needs some fresh debate as
to what actually should be considered correct behavior. Returning a random
(from the POV of the calling object) UNrelated row doesn't strike me as
very correct.

Cheers