On Tue, Mar 17, 2009 at 9:55 PM, Jakub Tutaj <j...@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/ _______________________________________________ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/