[Dbix-class] Re: column order in unions [patch?]

[Ian Sillitoe]

Urrrgghhh... sorry - please ignore previous email.

This was simply a fail on my behalf.

For the record:

sub swap_query_and_match {
    my $self =3D shift;

    my @cols        =3D $self->result_source->columns;
    my @mapped_cols =3D map {
                           if    (/^match_/) { s/match_/query_/ }
                           elsif (/^query_/) { s/query_/match_/ }
                           $_;
                        } @cols;

    return $self->search_rs(
        undef,
        {
            select =3D> \@mapped_cols,
            as     =3D> \@cols,
        }
    );
}

Should read:

sub swap_query_and_match {
    my $self =3D shift;

    my @cols        =3D $self->result_source->columns;
    my @mapped_cols =3D map {
                           my $col =3D $_;
                           if    ($col =3D~ /^match_/) { $col =3D~
s/match_/query_/ }
                           elsif ($col =3D~ /^query_/) { $col =3D~
s/query_/match_/ }
                           $col;
                        } @cols;

    return $self->search_rs(
        undef,
        {
            select =3D> \@mapped_cols,
            as     =3D> \@cols,
        }
    );
}


Apologies if that ended up wasting anyone's time...


-- =

Dr Ian Sillitoe
Orengo Group, Structural and Molecular Biology
University College London







On 27 March 2012 12:48, Ian Sillitoe <ian at sillit.com> wrote:

> Hello all,
>
> Apologies if this is long winded - I figured it might be useful to give
> some context.  I have a table that can be simplified to:
>
> SCORES( query_id, match_id, query_length, match_length, score )
>
> which contains scores for an all against all comparison. It is only a
> half-matrix - to return all scores for a particular 'id' I use a union
> where one half searches for query_id =3D $ID and the other searches for
> match_id =3D $ID (and the latter swaps all the query/match fields around =
so
> the id in question is always 'query' not 'match') - i.e.
>
> SELECT
>   query_id, match_id, query_length, match_length, score
> FROM
>   scores
> WHERE
>   query_id =3D "$ID" and match_id !=3D "$ID"
>
> UNION
>
> SELECT
>    query_id as match_id,
>   match_id as query_id,
>   query_length as match_length,
>   match_length as query_length,
>   score
>  FROM
>   scores
> WHERE
>   query_id !=3D "$ID" and match_id =3D "$ID"
>
> I originally had defined this as a view on a custom result source which
> worked fine in DBIC. However it's a bit of a pain to maintain (in reality
> there are more query and match related fields) - so on a recent code tidy=
 I
> figured it would be better to try and do the union properly in DBIC.
>
> I was trying to get something like:
>
> My::Schema->resultset( 'Scores' )->force_query_id( $ID )->all;
>
> Done with:
>
> package My::Schema::ResultSet::Scores;
> use My::Moose;                          # provides Moose, Types and
> MooseX::Params::Validate
> extends 'DBIx::Class::ResultSet';
>
> __PACKAGE__->load_components(qw{ Helper::ResultSet::SetOperations });
>
> sub force_query_id {
>     my $self =3D shift;
>     my ( $domain_id ) =3D pos_validated_list( \@_,
>             { isa =3D> 'DomainID', coerce =3D> 1 },
>     );
>
>     my $rs1 =3D $self->search_rs(
>             { query_id =3D> $domain_id, match_id =3D> { '!=3D' =3D> $doma=
in_id } },
>         );
>
>     my $rs2 =3D $self->search_rs(
>             { query_id =3D> { '!=3D' =3D> $domain_id }, match_id =3D> $do=
main_id }
>         )->swap_query_and_match;
>
>     return $rs1->union( $rs2 );
> }
>
>
> sub swap_query_and_match {
>     my $self =3D shift;
>
>     my @cols        =3D $self->result_source->columns;
>     my @mapped_cols =3D map {
>                            if    (/^match_/) { s/match_/query_/ }
>                            elsif (/^query_/) { s/query_/match_/ }
>                            $_;
>                         } @cols;
>
>     return $self->search_rs(
>         undef,
>         {
>             select =3D> \@mapped_cols,
>             as     =3D> \@cols,
>         }
>     );
> }
>
> However, this was throwing:
>
> ResultSets do not all have the same selected columns! called at
> DBIx/Class/Helper/ResultSet/SetOperations.pm line 71
>
> Which seems to be because the union needs the list of resolved 'as'
> attributes to be identical between all resultsets involved in the union.
>
> # DBIx/Class/Helper/ResultSet/SetOperations.pm:71
>
> $self->throw_exception("ResultSets do not all have the same selected
> columns!")
>          unless $self->_compare_arrays($as, $attrs->{as});
>
> I can see that the fields need to be identical, but does it matter about
> the order? I changed my local version to:
>
>      $self->throw_exception("ResultSets do not all have the same selected
> columns!" )
>          unless $self->_compare_arrays([sort @$as], [sort
> @{$attrs->{as}}]);
>
> which now works for me (and passes the DBIx::Class::Helpers tests), but
> I'm not sure whether that was there for a reason - is that patch sane?
>
> Many thanks,
>
> --
> Dr Ian Sillitoe
> Orengo Group, Structural and Molecular Biology
> University College London
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120327/a4f=
01ec6/attachment.htm