DaVluuu DaVluuu - 4 months ago 26
MySQL Question

Eloquent - join table on itself

I'm trying to join a table on itself but keep getting errors. Here is my current code. I've also tried Raw statement. Not sure which direction to go in at the moment.

My code:

$Calle = db('VoipBill')
->table('billing')
->join('billing', 'billing.srcnum', '=', 'billing.dstnum')
->where('acct_name', '100080_company')
->where('srcnum', $call->srcnum)
->whereBetween('calldate', [$set_time_lower, $set_time_upper])
->get();


This is the error:


SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique
table/alias: 'billing' (SQL: select * from
billing
inner join
billing
on
billing
.
srcnum
=
billing
.
dstnum
where
acct_name

= 100080_company and
srcnum
= +27******** and
calldate
between 2016-05-02 09:19:27 and 2016-05-02 09:19:37)

Answer

You have to alias the tables. Try it it this way:

    $Calle = db('VoipBill')
        ->table('billing as bsrc')
        ->join('billing as bdst', 'bsrc.srcnum', '=', 'bdst.dstnum')
        ->where('bsrc.acct_name', '100080_company')
        ->where('bsrc.srcnum', $call->srcnum)
        ->whereBetween('bsrc.calldate', [$set_time_lower, $set_time_upper])
        ->get();
Comments