Discussion:
LEFT OUT JOIN with custom condition/bind value
(too old to reply)
Sven Eppler
2012-11-24 15:26:37 UTC
Permalink
Hi There,

i've been searching all along the internet.

So this List is my last resort. ;)

I'v got Users in Groups. Now i want to eMail all Users not in Group
"Admins". So i need something similar like this:

SELECT u.email, u.name FROM Users u
LEFT OUTER JOIN Groups g ON u.id = g.uid AND g.id = ADMIN-GROUP-ID
WHERE g.id IS NULL

After searching around this COULD easily be done with a relationship,
beside the problem, that you cannot give in the custom-group id. So i
could only create a relationship like "all_users_not_in_admingroup" but
can't create a general relationsship like "all_users_not_in_groupX"?

Any ideas on how to solve this problem with DBIC?

I know about the CODEREF-Option for defining Relationship-Conditions.
But they don't seem to solve my problem, right?

Greetings,
Sven
Hailin Hu
2012-11-25 14:57:28 UTC
Permalink
Hi, Sven

I'm afraid it is hard to define a conditional relationship with DBIx::Class.
But you can rewrite the condition in JOIN clause with WHERE clause in
most of cases.

SELECT u.email, u.name FROM Users u
LEFT OUTER JOIN Groups g ON u.id = g.uid
WHERE g.id = ADMIN-GROUP-ID

Or you can have a look at VIEW approach.
Post by Sven Eppler
Hi There,
i've been searching all along the internet.
So this List is my last resort. ;)
I'v got Users in Groups. Now i want to eMail all Users not in Group
SELECT u.email, u.name FROM Users u
LEFT OUTER JOIN Groups g ON u.id = g.uid AND g.id = ADMIN-GROUP-ID
WHERE g.id IS NULL
After searching around this COULD easily be done with a relationship,
beside the problem, that you cannot give in the custom-group id. So i
could only create a relationship like "all_users_not_in_admingroup" but
can't create a general relationsship like "all_users_not_in_groupX"?
Any ideas on how to solve this problem with DBIC?
I know about the CODEREF-Option for defining Relationship-Conditions.
But they don't seem to solve my problem, right?
Greetings,
Sven
_______________________________________________
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/
Peter Rabbitson
2012-11-25 15:20:11 UTC
Permalink
Post by Hailin Hu
Hi, Sven
I'm afraid it is hard to define a conditional relationship with DBIx::Class.
But you can rewrite the condition in JOIN clause with WHERE clause in
most of cases.
An inner join - yes. A left join - never. The WHERE and ON clauses affect the
resultset quite differently in the case of a left join.
Peter Rabbitson
2012-11-25 15:27:50 UTC
Permalink
Post by Sven Eppler
I know about the CODEREF-Option for defining Relationship-Conditions.
But they don't seem to solve my problem, right?
Not entirely. The coderef is invoked every time a query is generated,
and the contents of the coderef are made of perl ;)

So the short-term ugly-as-fuck-but-usable solution is:

...->has_many(... sub {
my $varcond = $My::Secret::Global::varcond
or die 'You forgot to set $My::Secret::Global::varcond';
...
});

and then:

my @res = do {
local $My::Secret::Global::varcond = 42;
$rs->search(...)->all;
};

Long term the plan is to have something along these lines[1]. The reason
this is not yet imlemented is the usual - tuits. It is also not a trivial
task as the underpinnings are not yet smart eniough to handle something
of this complexity.

Cheers

[1] https://github.com/dbsrgits/dbix-class/blob/rfc/todo_list/lib/DBIx/Class/Manual/Dev/TODO.pod#relationship-arguments
Aaron Trevena
2012-11-25 21:40:19 UTC
Permalink
Post by Peter Rabbitson
Post by Sven Eppler
I know about the CODEREF-Option for defining Relationship-Conditions.
But they don't seem to solve my problem, right?
Not entirely. The coderef is invoked every time a query is generated,
and the contents of the coderef are made of perl ;)
...->has_many(... sub {
my $varcond = $My::Secret::Global::varcond
or die 'You forgot to set $My::Secret::Global::varcond';
...
});
local $My::Secret::Global::varcond = 42;
$rs->search(...)->all;
};
Long term the plan is to have something along these lines[1]. The reason
this is not yet imlemented is the usual - tuits. It is also not a trivial
task as the underpinnings are not yet smart eniough to handle something
of this complexity.
Presumably you could use a method in the resultset to take an arg and
wrap it transparently so you never see the fugly in the rest of your
code?

A
--
Aaron J Trevena, BSc Hons
http://www.aarontrevena.co.uk
LAMP System Integration, Development and Consulting
Peter Rabbitson
2012-11-26 06:50:12 UTC
Permalink
Post by Aaron Trevena
Post by Peter Rabbitson
Post by Sven Eppler
I know about the CODEREF-Option for defining Relationship-Conditions.
But they don't seem to solve my problem, right?
Not entirely. The coderef is invoked every time a query is generated,
and the contents of the coderef are made of perl ;)
...->has_many(... sub {
my $varcond = $My::Secret::Global::varcond
or die 'You forgot to set $My::Secret::Global::varcond';
...
});
local $My::Secret::Global::varcond = 42;
$rs->search(...)->all;
};
Long term the plan is to have something along these lines[1]. The reason
this is not yet imlemented is the usual - tuits. It is also not a trivial
task as the underpinnings are not yet smart eniough to handle something
of this complexity.
Presumably you could use a method in the resultset to take an arg and
wrap it transparently so you never see the fugly in the rest of your
code?
Of course, hence the "made of perl" part. The only thing I am pointing out
is that DBIC itself does not yet provide a *scalable* solution for this,
and punts situation-specific micro-solutions to the user.

Naturally patches implementing the feature as described in the link I
gave earlier are beyond welcome.

Cheers

Loading...