Re: [Dbix-class] Left join with an extra condition

2009-10-25 Thread Peter Rabbitson 
On Sat, Oct 24, 2009 at 01:43:33PM -0700, Bill Moseley wrote:
 On Sat, Oct 24, 2009 at 8:24 AM, Wallace Reis wall...@reis.org.br 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@lists.scsys.co.uk

Re: [Dbix-class] Left join with an extra condition

2009-10-25 Thread Peter Rabbitson 
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   Mike2001
4   Mike2002
5   Mike2003


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@lists.scsys.co.uk

Re: [Dbix-class] Left join with an extra condition

2009-10-25 Thread Bill Moseley 
Hi Peter,

Thanks for helping.

On Sun, Oct 25, 2009 at 6:54 AM, Peter Rabbitson
rabbit+d...@rabbit.usrabbit%2bd...@rabbit.us
 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
mose...@hank.org
___
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@lists.scsys.co.uk

Re: [Dbix-class] Left join with an extra condition

2009-10-25 Thread Bill Moseley 
On Sun, Oct 25, 2009 at 6:54 AM, Peter Rabbitson
rabbit+d...@rabbit.usrabbit%2bd...@rabbit.us
 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
mose...@hank.org
___
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@lists.scsys.co.uk

Re: [Dbix-class] Left join with an extra condition

2009-10-24 Thread Peter Rabbitson 
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@lists.scsys.co.uk

Re: [Dbix-class] Left join with an extra condition

2009-10-24 Thread Bill Moseley 
On Sat, Oct 24, 2009 at 5:59 AM, Peter Rabbitson
rabbit+d...@rabbit.usrabbit%2bd...@rabbit.us
 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.pmhttp://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
mose...@hank.org
___
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@lists.scsys.co.uk

Re: [Dbix-class] Left join with an extra condition

2009-10-24 Thread Wallace Reis 

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@lists.scsys.co.uk

Re: [Dbix-class] Left join with an extra condition

2009-10-24 Thread Bill Moseley 
On Sat, Oct 24, 2009 at 8:24 AM, Wallace Reis wall...@reis.org.br 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
mose...@hank.org
___
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@lists.scsys.co.uk

Re: [Dbix-class] Left join with an extra condition

2009-10-24 Thread 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@lists.scsys.co.uk

[Dbix-class] Left join with an extra condition

2009-10-23 Thread 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.*

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

Thanks,

-- 
Bill Moseley
mose...@hank.org
___
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@lists.scsys.co.uk