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 created 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 want to get the 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 am trying to translate the above SQL statement into 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 an exception "No link ARRAY" while I try to execute the following result. Please note that I was not allowed to use the connection in DBIx :: Class, as tables of referrers and branches, is not related to tables * table ***.

How can I get DBIx :: Class code equivalent to a table selected by multiple SQL?

Answer 1

DBIx WHERE FROM. , DBIx:

$bom_schema->resultset('View_Of_Table1_And_Table2_And_Referrers_And_Affiliates');

Answer 2

1/2 /, DBIx:: Class?

, , - Table1:

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

2:

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

.

, Schema:: Loader , , ? DBIx:: Class , , FK, .

.