[Catalyst] LEFT JOIN with AND statement

[Jakub Tutaj]

Zbigniew Lukasiak wrote:
> 
> 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)
> 
> 
Hi!
I tried the Cookbook way, but Catalyst couldn't find register_extra_source
function...so from there I searched for that problem and found this link:

http://www.perlmonks.org/?node_id=633800

Basically, I've created file GroupTasksComplex and put it into Schema
folder. It looks like this:

package saps::Schema::GroupTasksComplex;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("group_tasks"); #this probably could be any name
__PACKAGE__->add_columns(
    qw/
    gt_id task_name gt_status gt_opentime gt_closetime gt_task_id gt_groupid
    id compilationstatus runstatus originality comment registered codesize
memoryusage group_task_id user_id
    /
); # list of column names you want to get in return

my $source = __PACKAGE__->result_source_instance()->name( \<<SQL);
(
SELECT group_tasks.id as gt_id, tasks.name as task_name,  group_tasks.status
as gt_status, group_tasks.opentime as gt_opentime, group_tasks.closetime as
gt_closetime, group_tasks.task_id as gt_task_id, group_tasks.group_id as
gt_groupid, student_tasks.* 
FROM group_tasks 
LEFT JOIN student_tasks 
ON group_tasks.id = student_tasks.group_task_id AND student_tasks.user_id =
? LEFT JOIN tasks ON group_tasks.task_id = tasks.id 
WHERE group_tasks.group_id = ? )
SQL


Then to get query in my controller I do:

my $studentgrouptasks = $c->stash->{studentgrouptasks} =
[$c->model('sapsDB::GroupTasksComplex')->search ( 
    {}, 
    {
        bind  => [ $c->user->id,$groupId ]
    }
) ];	

I hope that will help someone else too. 

Thanks Zbigniew for a hint!

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