jeudi 8 juin 2017

Create a query scope with two databases within the same scope in Laravel

I am trying to create a query scope with one table from the first database and attaching a left join to another table from another database. I understand cleary how to do a query scope in Laravel.

Is it possible to left join 2 tables from different databases? If so, how do you do it?

Here is my query scope:

public function scopeLinkSalesHistory($query, $search)
{
    $query->leftJoin('sOeinvd', 'inventory.item', '=', 'sOeinvd.item')
          ->leftJoin('sOeinvh', 'sOeinvh.invuniq', '=', 'sageOeinvd.invuniq');

    ! ( empty( $search ) ) ? $query->where( function ( $query ) use ( $search ) {
        $query->where( 'inventory.item', 'LIKE', '%' . $search . '%' )
              ->orWhere( 'inventory.description', 'LIKE', '%' . $search . '%' );
    } )
        : $query;

    $query->select('*', DB::raw('sum(qtyshipped) as qtysold'), 'inventory.item as item');
    $query->groupBy('sOeinvh.invfiscper')->groupBy('sOeinvh.invfiscyr')->groupBy('inventory.item')->orderBy('inventory.item')->orderBy('sOeinvh.invfiscyr')->orderBy('sOeinvh.invfiscper');
    return $query;
}

The tables that start with 's' are from the second database. It doesn't work because I am not referencing these tables properly. Normally I would use this:

DB::connection('sql')->table('sOeinvd')

But how do you use this in a query scope?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire