Select from multiple tables in DBIx::Class

Question

I have the following DBIx::Class code :

my $where = 'me.loginid = ? AND me.code = ?';
my @bind  = ( $loginID, $code );
my $tip_signals = $bom_schema->resultset('Table1')->search_literal(
    $where, @bind,
    {
        join => 'table2',
        group_by => [ 'me.id' ],
        '+select' => [ {'count' => '*'}, 'table2.id' ],
        '+as'     => [ 'Count', 'ID' ],
    });

The following SQL statement was generated based on the above DBIx::Class code:

SELECT me.id, me.loginid, me.tipster_date, me.code, me.short_code_without_payout, COUNT( * ), table2.id 

FROM table1 me LEFT JOIN table2 table2 ON table2.tip_signals_id = me.id 

WHERE ( 
me.loginid = 'yccheok' AND me.code = 'ALIBABA_CODE' 
) 

GROUP BY me.id

Now, I wish to get result from 4 tables. I write my own SQL code manually :

SELECT me.id, me.loginid, me.tipster_date, me.code, me.short_code_without_payout, COUNT( * ), table2.id 

FROM table1 me, table2, referrers, affiliates 

WHERE ( 
me.loginid = 'yccheok' AND me.code = 'ALIBABA_CODE'

and table2.tip_signals_id = me.id
and referrers.affiliates_id = affiliates.id
and affiliates.loginid = me.loginid
and referrers.loginid = table2.loginid
) 

GROUP BY me.id

I try to translate above SQL statement to DBIx::Class as follows:

my $where = 'me.loginid = ? AND me.code = ? AND table2.tip_signals_id = me.id AND referrers.affiliates_id = affiliates.id AND affiliates.loginid = me.loginid AND referrers.loginid = table2.loginid';
my @bind  = ( $loginID, $code );
my $tip_signals = $bom_schema->resultset('Table1')->search_literal(
    $where, @bind,
    {
        from     =>  [ {table2=>'table2'}, {referrers=>'referrers'}, {affiliates=>'affiliates'} ],
        group_by  =>  [ 'me.id' ],
        '+select'  =>  [ {'count' => '*'}, 'table2.id' ],
        '+as'      =>  [ 'Count', 'ID' ],
    });

I get exception "Not an ARRAY reference" while I try to perform next on resultset. Please note that, I was not allowed to use join in DBIx::Class, as referrers and affiliates tables, has no relationship with *table*** tables.

How can I have DBIx::Class code equivalent to SQL multiple selected table?

Answer 1

If you don't have relationships between table1/2 and referrers/affiliates, then why not create them in your DBIx::Class result classes?

You didn't paste those, so I'm making a guess here, you want to add something like this to your Table1 class:

__PACKAGE__->has_many('affiliates', 'MyDB::Schema::Affiliate', 'loginid');

And in Table2:

__PACKAGE__->has_many('referrers', 'MyDB::Schema::Referrer', 'loginid');

Fix up those class names to match your actual classes.

I'll make a guess that you used the Schema::Loader to load your tables, and it didn't create these for you as your database doesn't have the appropriate foreign keys set up? DBIx::Class doesn't care, you can quite happily set up more relationships in it, that aren't actual FKs, and it will create the joins you need for you.

HTH.

Answer 2

Instead of hand coded complicated query in DBIx, I can simply create a view to map up those WHERE and FROM statement. Then, by just using single line of DBIx :

$bom_schema->resultset('View_Of_Table1_And_Table2_And_Referrers_And_Affiliates');