Re: [Dbix-class] Left join with an extra condition
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
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
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
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
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
