Joining the same table several times with different conditions each join

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Joining the same table several times with different conditions each join

Andrew Beverley
Dear all,

I'd like to join the same table multiple times, with different
conditions each join (and a variable number of joins). Equivalent SQL to
something like this:

   SELECT record.id,
          s1.value   AS firstname,
          s2.value   AS surname
     FROM record
LEFT JOIN string s1  ON s1.record_id=record.id
                    AND s1.schema_id=1
LEFT JOIN string s2  ON s2.record_id=record.id
                    AND s2.schema_id=2;
LEFT JOIN ...

Note: the number of LEFT JOINs is variable.

Joining with a custom join condition has been discussed before:

http://lists.scsys.co.uk/pipermail/dbix-class/2014-January/011566.html

The workaround as above was (still is?) to use a global variable.
However, I'm not sure that workaround can be used for the scenario
described above. What's the best way (if any) of achieving this with
DBIx::Class?

Of course, the table could be designed with multiple columns for each of
the values (surname and firstname being just 2 examples), but these are
not known at the time of coding and are configurable by the application.

Thanks,

Andy



_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: Joining the same table several times with different conditions each join

Karen Etheridge
On Sat, May 17, 2014 at 11:01:59AM +0100, Andrew Beverley wrote:
> I'd like to join the same table multiple times, with different
> conditions each join (and a variable number of joins).

If you define each of your join permutations as a relationship, you can
bring in the joins using that/those relationship name(s):

$rs->search({ ... }, { join => [ 'rel1', 'rel2', ... ] })

https://metacpan.org/pod/DBIx::Class::ResultSet#join

> Of course, the table could be designed with multiple columns for each of
> the values (surname and firstname being just 2 examples), but these are
> not known at the time of coding and are configurable by the application.

You can dynamically add relationship definitions at runtime.  Just keep the
rel names around in a local variable or attribute for later use.

Sorry if I am missing something complicated that would prevent this from
working -- as it seems pretty straightforward to me! :)



_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: Joining the same table several times with different conditions each join

Andrew Beverley
On Sat, 2014-05-17 at 10:00 -0700, Karen Etheridge wrote:
> On Sat, May 17, 2014 at 11:01:59AM +0100, Andrew Beverley wrote:
> > I'd like to join the same table multiple times, with different
> > conditions each join (and a variable number of joins).
>
> If you define each of your join permutations as a relationship, you can
> bring in the joins using that/those relationship name(s):
>
> $rs->search({ ... }, { join => [ 'rel1', 'rel2', ... ] })

The problem is the second join condition, which requires more than a
simple join. However...

> > Of course, the table could be designed with multiple columns for each of
> > the values (surname and firstname being just 2 examples), but these are
> > not known at the time of coding and are configurable by the application.
>
> You can dynamically add relationship definitions at runtime.  Just keep the
> rel names around in a local variable or attribute for later use.

...this could be the answer.

I could do with a bit of help with dynamically adding a relationship at
runtime though.

The code below is what I've written in my main application (not in a
schema class). I'm assuming it's not correct and that I don't need to
add both relationship statements; however, I couldn't get it to work
correctly with only one or the other.

What's the correct way of dynamically adding a relationship?

$sch->source('Record')->add_relationship(
    surname => 'String',
    sub {
        my $args = shift;

        return {
            "$args->{foreign_alias}.record_id" =>
                { -ident => "$args->{self_alias}.id" },
            "$args->{foreign_alias}.layout_id" => 1,
        };
    }
);
SDS::Schema::Result::Record->has_one(
    surname => 'String',
    sub {
        my $args = shift;

        return {
            "$args->{foreign_alias}.record_id" =>
                { -ident => "$args->{self_alias}.id" },
            "$args->{foreign_alias}.layout_id" => 1,
        };
    }
);

Thanks,

Andy



_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...