Good day, I'm trying to do this query in eloquent and I can't make it work

I have 2 query models in sqlserver

which different databases

1 for laravel users and configurations

and another SAP BUSSINES ONE database which I want to consult the stock of warehouses

where I have the authentication of the users it has nothing to do with the other database I only want to consult data data from the sap

but I want to use several tables from the alternate database

and I have done one but it repeats the data on the number of stores, etc. The other is a failure

the last one is the query i need

if you could help me i would appreciate it

1st model

class Almacen extends Model
{
    use HasFactory;

    Public function relaciona()
    {
        return $this->belongsToMany('App\Models\Stock');
    }

    protected $connection = 'sqlsrv2';
    protected $table = 'OITW';

}

2nd model

class Stock extends Model
{
    use HasFactory;

    Public function relacions()
    {
        return $this->belongsToMany(Almacen::class);
    }
    
    protected $connection = 'sqlsrv2';
    protected $table = "OITM";
}

the one that works for me but repeats data the query is made from the controller

$Stock =  Stock::select(
        'OITM.ItemCode',
        'OITM.ItemName',
        'OITM.OnHand',
        'OITW.WhsCode',
        'OITM.SalUnitMsr'
        /* ,'OBTN.ExpDate' */
    )
   //->crossJoin('OBTN')
   ->crossJoin('OITW')
   //->groupBy('OITM.ItemCode')
   //->select('OITM.ItemCode','OITM.Itemname','OITM.OnHand')
   ->where('OITW.ItemCode','=',Almacen::raw('OITM.ItemCode'))
   //->where('OITW.WhsCode',/* '=', *//* '01', */'01')
   //->where('OITM.OnHand','>',0.00)
   //->where('OBTN.ExpDate','=' ,'2022-08-08')
   -> get();

return view('stock.index', compact('Stock'));

correct query

SELECT
    T0.[WhsCode],
    T1.[WhsName],
    T0.[OnHand],
    T2.[ItemCode],
    T2.[ItemName]
FROM
    OITW T0
    INNER JOIN
        OWHS T1 ON T0.[WhsCode] = T1.[WhsCode]
    INNER JOIN
        OITM T2 ON T0.[ItemCode] = T2.[ItemCode]
ORDER BY T0.[WhsCode]

CodePudding user response：

A couple of things, more related to code review than your question.

• You should leave your properties at the beginning of your classes, and then go on to declaring the methods.
• The correct keyword is public, not Public. Windows might let you get away with this because it's case insensitive, but a unix server will probably give you an error.
• You can use Model::class in your relationship methods. You do it for Stock but not for Almacen. You should be consistent.
• From the query you gave, I'm not sure these two models have an M:N relationship, so the belongsToMany method might not be the correct one.

class Almacen extends Model
{
    use HasFactory;

    protected $connection = 'sqlsrv2';
    protected $table = 'OITW';

    public function relaciona()
    {
        return $this->belongsToMany(Stock::class);
    }

}

class Stock extends Model
{
    use HasFactory;

    protected $connection = 'sqlsrv2';
    protected $table = "OITM";

    public function relacions()
    {
        return $this->belongsToMany(Almacen::class);
    }    
}

As for the query, you're not using CROSS JOIN but INNER JOIN, so join() should be used.

It's a rather straightforward query so it's going to look practically identical in the query builder.

$results = DB::connection('sqlsrv2')->query() // specify connection if sqlsrv2 is not the default
    ->select(
        'T0.[WhsCode]',
        'T1.[WhsName]',
        'T0.[OnHand]',
        'T2.[ItemCode]',
        'T2.[ItemName]'
    )
    ->from('OITW', 'T0')
    ->join('OWHS T1', 'T0.[WhsCode]', 'T1.[WhsCode]')
    ->join('OITM T2', 'T0.[ItemCode]', 'T2.[ItemCode]')
    ->orderBy('T0.[WhsCode]')
    ->get();

This returns a Collection of objects. If you want it to return an Eloquent Collection of Models, use your Model instead of the DB facade.

$results = Stock::query() // connection is specified in the model
    ->select(
        'T0.[WhsCode]',
        'T1.[WhsName]',
        'T0.[OnHand]',
        'T2.[ItemCode]',
        'T2.[ItemName]'
    )
    ->from('OITW', 'T0') // specify the table again so we can alias it as T0
    ->join('OWHS T1', 'T0.[WhsCode]', 'T1.[WhsCode]')
    ->join('OITM T2', 'T0.[ItemCode]', 'T2.[ItemCode]')
    ->orderBy('T0.[WhsCode]')
    ->get();

CodePudding user response：

Thanks brother, your support was helpful yes, I'm not very expert in laravel it confuses me a lot of things still but learning the code I adjusted it and so it worked for me.

$Stock =  DB::connection('sqlsrv2')->query() // connection is specified in the model
      
      ->SELECT( 
        'T0.WhsCode', 
        'T1.WhsName', 
        'T0.OnHand', 
        'T2.ItemCode', 
        'T2.ItemName' 
        )
      ->FROM( 'OITW' , 'T0' ) 
      ->JOIN ('OWHS AS T1' , 'T0.WhsCode' , '=', 'T1.WhsCode' )
      ->JOIN ('OITM AS T2' , 'T0.ItemCode', '=', 'T2.ItemCode' )
      ->ORDERBY ('T0.WhsCode')
      ->get();