Left join with an extra condition

[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.*, 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
[hidden email]

_______________________________________________
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@...

[Peter Rabbitson-2]

On Fri, Oct 23, 2009 at 12:45:01PM -0700, Bill Moseley wrote:
> 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

_______________________________________________
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@...

[Bill Moseley]

On Sat, Oct 24, 2009 at 5:59 AM, Peter Rabbitson <[hidden email]> wrote:

> 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...)
 

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

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
[hidden email]

_______________________________________________
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@...

[Wallace Reis-2]

On 24/10/2009, at 11:48, Bill Moseley wrote:

> 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

_______________________________________________
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@...

[Bill Moseley]

On Sat, Oct 24, 2009 at 8:24 AM, Wallace Reis <[hidden email]> wrote:

On 24/10/2009, at 11:48, Bill Moseley wrote:

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).

Are you talking about creating a database view that can be updated?

 

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
[hidden email]

_______________________________________________
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@...

[Darren Duncan]

Bill Moseley wrote:

> 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

_______________________________________________
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@...

[Rob Kinyon]

On Sat, Oct 24, 2009 at 20:18, Darren Duncan <[hidden email]> wrote:

> Bill Moseley wrote:
>>
>> 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

_______________________________________________
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@...

[Peter Rabbitson-2]

On Sat, Oct 24, 2009 at 01:43:33PM -0700, Bill Moseley wrote:

> On Sat, Oct 24, 2009 at 8:24 AM, Wallace Reis <[hidden email]> wrote:
>
> > On 24/10/2009, at 11:48, Bill Moseley wrote:
> >
> >  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).
> >
>
> Are you talking about creating a database view that can be updated?
>
>
>
> > 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

_______________________________________________
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@...

[Peter Rabbitson-2]

On Sat, Oct 24, 2009 at 05:18:12PM -0700, Darren Duncan wrote:

> Bill Moseley wrote:
>> 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

_______________________________________________
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@...

[Bill Moseley]

Hi Peter,

Thanks for helping.

On Sun, Oct 25, 2009 at 6:54 AM, Peter Rabbitson <[hidden email]> wrote:
 

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
[hidden email]

_______________________________________________
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@...

[Bill Moseley]

On Sun, Oct 25, 2009 at 6:54 AM, Peter Rabbitson <[hidden email]> wrote:

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
[hidden email]

_______________________________________________
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@...