[Catalyst] LEFT JOIN with AND statement

[Zbigniew Lukasiak]

On Tue, Mar 17, 2009 at 9:55 PM, Jakub Tutaj <jamz at wp.pl> wrote:
>
> Hello!
>
> I'd like to prepare query like:
> SELECT group_tasks.*,student_tasks.*
> FROM group_tasks
> LEFT JOIN student_tasks
> ON group_tasks.id = student_tasks.group_task_id AND student_tasks.user_id =
> 1
> WHERE group_tasks.group_id = 1
>
> I stuck at this point:
>
> $c->stash->{studentgrouptasks} = [$c->model('sapsDB::GroupTasks')->search(
>    {
>        'me.group_id' => 1,
>    },
>    {
>        join => [qw/ studenttasks/]
>    }
> )];
>
> I don't know where can I put 'AND student_tasks.user_id = 1' so it's
> corresponding with 'join'. I'd like to have list of all grouptasks designed
> for the student and if student already did the task (there's a studenttask
> record in DB) I also want info about it. That's why I need this 'AND' in
> JOIN .. ON statement, instead of normal JOIN .. ON .. WHERE clause. Any
> help?
>
> My tables are something like this:
>
> GroupTasks:
> id
> status
> task_id
> group_id
>
> StudentTasks:
> id
> status
> group_task_id
> user_id
>
> has_many and belongs_to :
>
> saps::Schema::GroupTasks->has_many(studenttasks =>
> 'saps::Schema::StudentTasks', 'group_task_id');
> saps::Schema::StudentTasks->belongs_to(grouptask =>
> 'saps::Schema::GroupTasks', 'group_task_id');
>
> Another question is, if I have proper query, how to distinct
> studenttasks.status and grouptasks.status using stashed 'studentgrouptasks'
> variable in TT (using FOREACH etc.)?

This is really not about Catalyst but rather about DBIC - so you might
want to redirect this question to the appriopriate list, but answering
your question I think the only way to do it is via a custom ResultSet
(http://search.cpan.org/~ribasushi/DBIx-Class-0.08099_07/lib/DBIx/Class/Manual/Cookbook.pod#Arbitrary_SQL_through_a_custom_ResultSource)
or using the from attribute
(http://search.cpan.org/~ribasushi/DBIx-Class-0.08012/lib/DBIx/Class/ResultSet.pm#from
- note the 'Use this on your own risk' warning).

Pozdr.
Zbyszek
http://brudnopis.blogspot.com/
http://perlalchemy.blogspot.com/