Discussion:
Left join with an extra condition
(too old to reply)
Bill Moseley
2009-10-23 19:45:01 UTC
Permalink
Looking for how to add an addition condition on a join. Here's an example
of the query with the extra condition in CAPS

select u.* <http://p.id/>, count(o.id)
from user u
left outer join job j on j.user = u.id AND J.JOB_TYPE = 6
where u.location = ?
group by u.*

So, wondering how to represent that along with the extra join condition.

Thanks,
--
Bill Moseley
***@hank.org
Peter Rabbitson
2009-10-24 12:59:49 UTC
Permalink
Post by Bill Moseley
Looking for how to add an addition condition on a join. Here's an example
of the query with the extra condition in CAPS
select u.* <http://p.id/>, count(o.id)
from user u
left outer join job j on j.user = u.id AND J.JOB_TYPE = 6
where u.location = ?
group by u.*
You can not do this natively yet. Your only option is a virtual view
resultset as described here:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08112/lib/DBIx/Class/ResultSource/View.pm
Bill Moseley
2009-10-24 14:48:15 UTC
Permalink
On Sat, Oct 24, 2009 at 5:59 AM, Peter Rabbitson
Post by Peter Rabbitson
Post by Bill Moseley
select u.* <http://p.id/>, count(o.id)
from user u
left outer join job j on j.user = u.id AND J.JOB_TYPE = 6
where u.location = ?
group by u.*
(nice how Gmail turned those columns into links...)
Post by Peter Rabbitson
You can not do this natively yet. Your only option is a virtual view
http://search.cpan.org/~ribasushi/DBIx-Class-0.08112/lib/DBIx/Class/ResultSource/View.pm<http://search.cpan.org/%7Eribasushi/DBIx-Class-0.08112/lib/DBIx/Class/ResultSource/View.pm>
Ok, thanks.

Can the virtual view inherit from the user? That is, can I convince DBIC
that the object returned is a real user object so I can update the object
and have it written to storage? The cookbook just says that the view cannot
be operated on. Obviously, it would be much more useful to have a real user
object.

This is not a very complex query -- I have many queries that I suspect may
require custom SQL. Was there any discussion of being able to add custom
SQL to a custom ResultSet class, for example? That way I could get back my
user object plus any additional columns from the custom SQL. A different
beast, or course, but this is something that was quite trivial to do with
Class::DBI.

Thanks for the help, Peter.
--
Bill Moseley
***@hank.org
Wallace Reis
2009-10-24 15:24:32 UTC
Permalink
Post by Bill Moseley
Can the virtual view inherit from the user? That is, can I convince
DBIC that the object returned is a real user object so I can update
the object and have it written to storage? The cookbook just says
that the view cannot be operated on. Obviously, it would be much
more useful to have a real user object.
You can do it if you use a DBMS with RULEs support (like PostgreSQL).
Or you can setup a belongs_to rel (where is_foreign_key_constraint =>
0) to the user and override qw/insert delete update/ row methods in
your view class to do such operations on ->referred_user instead of
$self, like you would do with RULEs on DBMS.

--
wallace reis/wreis Catalyst and DBIx::Class consultancy
with a clue
Software Engineer and a commit bit: http://shadowcat.co.uk/catalyst/
Shadowcat Systems Limited
http://www.shadowcat.co.uk http://www.linkedin.com/in/wallacereis
Bill Moseley
2009-10-24 20:43:33 UTC
Permalink
Post by Bill Moseley
Can the virtual view inherit from the user? That is, can I convince DBIC
Post by Bill Moseley
that the object returned is a real user object so I can update the object
and have it written to storage? The cookbook just says that the view cannot
be operated on. Obviously, it would be much more useful to have a real user
object.
You can do it if you use a DBMS with RULEs support (like PostgreSQL).
Are you talking about creating a database view that can be updated?
Post by Bill Moseley
Or you can setup a belongs_to rel (where is_foreign_key_constraint => 0) to
the user and override qw/insert delete update/ row methods in your view
class to do such operations on ->referred_user instead of $self, like you
would do with RULEs on DBMS.
That seems like a complex approach for something seemingly simple. DBIC
allows fetching additional columns when using database functions (e.g
length() ). And my query is returning just a user row and a count. Would
not a simple approach be to run the query and then populate user objects? I
guess I won't have result set to use for count or paging results.

I'm wondering if I'm missing something. Is it really true that with
DBIx::Class if you need a list of objects, but the query to fetch that list
cannot be represented by a search on a resultset then there's no way to use
SQL directly to get the set of objects?

Thanks,
--
Bill Moseley
***@hank.org
Peter Rabbitson
2009-10-25 13:54:05 UTC
Permalink
Post by Bill Moseley
Post by Bill Moseley
Can the virtual view inherit from the user? That is, can I convince DBIC
Post by Bill Moseley
that the object returned is a real user object so I can update the object
and have it written to storage? The cookbook just says that the view cannot
be operated on. Obviously, it would be much more useful to have a real user
object.
You can do it if you use a DBMS with RULEs support (like PostgreSQL).
Are you talking about creating a database view that can be updated?
Post by Bill Moseley
Or you can setup a belongs_to rel (where is_foreign_key_constraint => 0) to
the user and override qw/insert delete update/ row methods in your view
class to do such operations on ->referred_user instead of $self, like you
would do with RULEs on DBMS.
That seems like a complex approach for something seemingly simple. DBIC
allows fetching additional columns when using database functions (e.g
length() ). And my query is returning just a user row and a count. Would
not a simple approach be to run the query and then populate user objects? I
guess I won't have result set to use for count or paging results.
I'm wondering if I'm missing something. Is it really true that with
DBIx::Class if you need a list of objects, but the query to fetch that list
cannot be represented by a search on a resultset then there's no way to use
SQL directly to get the set of objects?
You have your terms mixed up, this is why it looks complex. The virtual view
is a *result source*. It will spawn *result sets* which will eventually become
SQL queries. The results coming back from the database will be interpreted
and stuffed into objects of the *result class* associated with the result set.

So depending on what you actually want as an end result you can:

1) Create a virtual view resultsource, and give it a specific result_class
via ->resultset_attributes (which will add the result_class setting to every
resultset class spawned from the virtual source)

2) You can take a resultset from your original source, and *temporarily* (i.e.
just for the life of this particular resultset object) substitute the 'from'
attribute with a scalarref of the SQL you want to execute. The 'from'
attribute represents everything between the FROM and WHERE keywords. There
used to be documentation of this attribute, but it was mostly factually
incorrect and thus was undocumented. However you might see how the scalarref
part worked here (the rest is now mostly irrelevant, as the format has changed,
A LOT): http://dev.catalyst.perl.org/svnweb/bast/revision/?rev=7711

Cheers
Bill Moseley
2009-10-26 00:02:01 UTC
Permalink
Hi Peter,

Thanks for helping.

On Sun, Oct 25, 2009 at 6:54 AM, Peter Rabbitson
Post by Peter Rabbitson
2) You can take a resultset from your original source, and *temporarily* (i.e.
just for the life of this particular resultset object) substitute the 'from'
attribute with a scalarref of the SQL you want to execute. The 'from'
attribute represents everything between the FROM and WHERE keywords. There
used to be documentation of this attribute, but it was mostly factually
incorrect and thus was undocumented. However you might see how the scalarref
part worked here (the rest is now mostly irrelevant, as the format has changed,
A LOT): http://dev.catalyst.perl.org/svnweb/bast/revision/?rev=7711
This seems to be the best solution for this specific problem.

I have the typical "music" database, with an extra table that tracks "gigs"
and the venue where those gigs are:

select * from artist;
id | name | label
----+--------------+-------
1 | artist one | 1
2 | artist two | 1
3 | artist three | 2
4 | artist four | 2


select * from gig order by artist;
id | artist | venue
----+--------+---------
1 | 1 | outside
2 | 1 | outside
3 | 1 | outside
4 | 1 | inside
5 | 2 | outside
6 | 2 | outside
7 | 2 | inside
8 | 2 | inside
9 | 3 | studio

And I want a count of how many "outside" gigs each artist plays:

SELECT
a.id, a.name,
count(g.id) as gig_count
FROM
artist a
LEFT JOIN gig g on g.artist = a.id AND g.venue = 'outside'
GROUP BY
1,2
ORDER BY a.id
id | name | gig_count
----+--------------+-----------
1 | artist one | 3
2 | artist two | 2
3 | artist three | 0
4 | artist four | 0

So, the custom ResultSet::Artist method is:

sub outside_gigs {
my $rs = shift;

return $rs->search( undef,
{
'select' => [
qw/ me.id me.name /,
{
count => 'gigs.id',
-as => 'gig_count',
},
],
from => \q{
artist me LEFT JOIN gig gigs
ON me.id = gigs.artist
AND gigs.venue = 'outside'
},

as => [qw/ id name gig_count / ],
group_by => '1,2',
},
);

}

What's good about this is the custom method is abstracted out into the
Artist's resultset class. That is, the implementation of outside_gigs() is
hidden. Plus, can make use of the ORM's features to limit rows and fetch a
given page:

my @artists = $schema->resultset('Artist')->search(
undef,
{
rows => 2,
page => 2,
order_by => 'id',
},
)->outside_gigs->all;

With the limitation that can't use a "join" or "prefetch". Plus, not sure
that will support a more complex query (e.g. where there's bind parameters
in a CASE in the SELECT list).
--
Bill Moseley
***@hank.org
Bill Moseley
2009-10-26 00:16:23 UTC
Permalink
On Sun, Oct 25, 2009 at 6:54 AM, Peter Rabbitson
Post by Peter Rabbitson
1) Create a virtual view resultsource, and give it a specific result_class
via ->resultset_attributes (which will add the result_class setting to every
resultset class spawned from the virtual source)
I'm not sure how practical that approach is. I've always thought of the
virtual views being of limited use for custom SQL.

I tried this approach for this specific problem. I did set the
"result_class" attribute and indeed got objects blessed into that class.
But, they didn't not function as those objects -- namely I could not call
relationship methods.

I suppose the relationships can be defined in the virtual view class, but
that's not very DRY if I have a large number of custom SQL views for the
same result class. Is there a way to make the custom views act just like
the result class?

I would find it more "natural" to define the custom SQL in my ResultSet
classes -- so that I could do:

@artist = $schema->resultset( 'Artist' )->outside_gigs;

But, I guess outside_gigs() can always proxy to the virtual view. Again,
it's not much use if the resulting objects don't act just like a normal
Artist object where relationships still work.

Thanks again for your help, Peter.
--
Bill Moseley
***@hank.org
Darren Duncan
2009-10-25 00:18:12 UTC
Permalink
Post by Bill Moseley
Looking for how to add an addition condition on a join. Here's an
example of the query with the extra condition in CAPS
select u.* <http://p.id/>, count(o.id <http://o.id>)
from user u
left outer join job j on j.user = u.id <http://u.id> AND J.JOB_TYPE = 6
where u.location = ?
group by u.*
So, wondering how to represent that along with the extra join condition.
What you are adding doesn't look like a normal join condition, as it isn't
comparing values in the 2 tables being joined.

On the other hand, any straight equality test certainly is expressible as a
join, if you consider the "6" to be a single-column,single-row table, and then
you are joining 3 tables.

Is the syntax you propose supposed to be a shorthand for that?

What DBMSs would support this syntax, as I've never seen it before?

-- Darren Duncan
Rob Kinyon
2009-10-25 02:48:18 UTC
Permalink
Post by Darren Duncan
Looking for how to add an addition condition on a join.  Here's an example
of the query with the extra condition in CAPS
select u.* <http://p.id/>, count(o.id <http://o.id>)
from user u
left outer join job j on   j.user = u.id <http://u.id> AND J.JOB_TYPE = 6
where u.location  = ?
group by u.*
So, wondering how to represent that along with the extra join condition.
What you are adding doesn't look like a normal join condition, as it isn't
comparing values in the 2 tables being joined.
On the other hand, any straight equality test certainly is expressible as a
join, if you consider the "6" to be a single-column,single-row table, and
then you are joining 3 tables.
Is the syntax you propose supposed to be a shorthand for that?
What DBMSs would support this syntax, as I've never seen it before?
MySQL, PG, and Oracle all support this. It's an inline WHERE condition.

Rob
Peter Rabbitson
2009-10-25 14:00:58 UTC
Permalink
Post by Darren Duncan
Post by Bill Moseley
Looking for how to add an addition condition on a join. Here's an
example of the query with the extra condition in CAPS
select u.* <http://p.id/>, count(o.id <http://o.id>)
from user u
left outer join job j on j.user = u.id <http://u.id> AND J.JOB_TYPE = 6
where u.location = ?
group by u.*
So, wondering how to represent that along with the extra join condition.
What you are adding doesn't look like a normal join condition, as it
isn't comparing values in the 2 tables being joined.
On the other hand, any straight equality test certainly is expressible as
a join, if you consider the "6" to be a single-column,single-row table,
and then you are joining 3 tables.
Is the syntax you propose supposed to be a shorthand for that?
What DBMSs would support this syntax, as I've never seen it before?
Any database that I have thrown it to so far has supported it (namely
MySQL, Pg, MSSQL, SQLite). The way rob explains it is rather limited -
this is not simply a WHERE condition. Consider:

Artists
-------
Bob
John
Mike


CDs
ID Artist Year
-------------------
1 Bob 2000
2 Bob 2001
3 Mike 2001
4 Mike 2002
5 Mike 2003


We want a list of all artists and all their cds - easy:

SELECT * FROM artist a LEFT JOIN cd c ON a.name = c.artist


Now we want a list of all artists and to see if they have
released a CD in the year 2000. The catch is that if there is
no year 2000 cd, we still want to see the artist name. This is
when you need the extra join condition, as WHERE will not cut it

SELECT * FROM artist a LEFT JOIN cd c ON a.name = c.artist AND c.year = 2000

The above will return you either a row with an artist and a cd
released in the year 2000, OR will return you the artist row and
NULLs where the CD would have been.

Cheers
Loading...