[Catalyst] LEFT JOIN with AND statement

[Jakub Tutaj]

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

Regards.
Jakub Tutaj

-- 
View this message in context: http://www.nabble.com/LEFT-JOIN-with-AND-statement-tp22567550p22567550.html
Sent from the Catalyst Web Framework mailing list archive at Nabble.com.