使Laravel模型使用复杂的查询作为表 [英] Making a Laravel Model use complex query as table

问题描述

我正在开发一个Web应用程序，该应用程序可与ERP生产数据库连接，并且通过具有许多联接和别名的复杂SQL查询来检索数据，然后再将其传递给我的控制器和视图，并以不同的方式对其进行修改.

I am developing a web app that interfaces with an ERP production database and the data is being retrieved by a complex SQL query with many joins and aliases before being passed to my controllers and views which modify them in different ways.

例如，结果可能是一系列产品，其中包含每次购买的所有相关信息，例如型号，序列号，客户等，而在较小规模的应用程序中，这些产品肯定都存储在同一个产品中桌子.

The result might be, for example, an array of products that contains all relevant information for each purchase such as model, serial number, customer, etc., and in a smaller scale app these would certainly all be stored in the same table.

但是，由于我要从非常复杂的ERP解决方案中提取信息，因此这些详细信息取自许多不同的表，但是我想将其视为模型中的单个表.

However because I am pulling from a very complex ERP solution these details are taken from many different tables, but I'd like to treat it as a single table in my model.

有什么办法可以做到这一点，以便我可以像往常一样使用Eloquent?像Product::all()之类的东西不起作用，尽管我可以调用诸如Product::getWhere($serial)之类的自定义方法，但我宁愿以雄辩的方式执行此操作，因为它更容易并且使分页更易于使用.

Is there any way to accomplish this so that I can use Eloquent as usual? Things like Product::all() do not work, though I can call custom methods like Product::getWhere($serial) but I'd rather do this the Eloquent way since it is easier and makes pagination much nicer to work with.

以下是我的查询内容的想法:

Here is an idea of what my queries looks like:

SELECT
[PSerials].[ElementID] AS [ProductElementID],
[PSerials].[SerialNumber] AS [SerialNumber],
[PSerials].[SNStatus] as [ProductStatus],
[PSerials].[JobID] AS [JobID],
[Element].[Desc] AS [Description],
[PSerials_UD].[Prog] AS [ProgramNum],
[PSerials_UD].[Elec] AS [ElecPrint],
[PSerials_UD].[SinglePHVolt_c] AS [SinglePHVolt],
[PSerials_UD].[SinglePHAmp_c] AS [SinglePHAmp],
[PSerials_UD].[ThreePHVolt_c] AS [ThreePHVolt],
[PSerials_UD].[ThreePHAmp_c] AS [ThreePHAmp],
[PSerials_UD].[Notes_c] AS [Notes],
[PSerials_UD].[WarrantyExpDate_c] AS [WarrantyExpDate],
[QuoteDtl].[QuoteNum] AS [QuoteNum],
[QuoteDtl].[QuoteComment] AS [QuoteComment],
[OrderDtl].[RequestDate] AS [ShipDate],
[Customer].[CustID] AS [CustomerID],
[Customer].[Name] AS [CustomerName],
[Customer1].[CustID] AS [ShipToCustomerID],
[Customer1].[Name] AS [ShipToCustomerName]
FROM Erp.PSerials AS PSerials
INNER JOIN Erp.Element AS Element ON
PSerials.Company = Element.Company AND PSerials.ElementID = Element.ElementID
AND (Element.ProdCode = 'MACH' AND Element.ClassID = 'MAC')
INNER JOIN Erp.PSerials_UD AS PSerials_UD ON
PSerials_UD.ForeignSysRowID = PSerials.SysRowID
LEFT OUTER JOIN Erp.PProd AS PProd ON PProd.JobID = PSerials.JobID
LEFT OUTER JOIN Erp.OrderDtl AS OrderDtl ON PProd.Company = OrderDtl.Company
AND PProd.OrderNum = OrderDtl.OrderNum AND PProd.OrderLine = OrderDtl.OrderLine
LEFT OUTER JOIN Erp.QuoteDtl AS QuoteDtl ON OrderDtl.QuoteNum = QuoteDtl.QuoteNum
AND OrderDtl.QuoteLine = QuoteDtl.QuoteLine
LEFT OUTER JOIN Erp.Customer AS Customer ON  OrderDtl.Company = Customer.Company
AND OrderDtl.CustNum = Customer.CustNum
LEFT OUTER JOIN Erp.Customer AS Customer1 ON PSerials.Company = Customer1.Company
AND PSerials.ShipToCustNum = Customer1.CustNum

我将其放在Product模型上的静态变量中，然后具有一些自定义函数，这些函数可以根据需要将SQL查询简单地添加到最后，但这并不优雅，并且使用起来并不有趣，这是我如何使用这些查询的示例:

I have it sitting in a static variable on my Product model and then have custom functions that simple tack on SQL queries to the end as needed, but this isn't elegant and it isn't fun to work with, here's an example of how I use these queries:

public static function getWhere($serial)
{
    $query = DB::select(DB::raw(Self::$baseQuery));
    $collection = new \Illuminate\Support\Collection($query);

    $product = $collection->where("SerialNumber", $serial)->first();

    return $product;
}

有人可以让我的模型像对待简单表那样简单地对待查询吗?或者也许还有另一种方法可以做到这一点?

Does anyone how I can have my model simply treat queries like that as if they were a simple table, or is there perhaps another way to go about doing this?

更新

由于下面的Zamrony P. Juhara的建议，我能够成功创建一个SQL视图，并告诉我要引用的Products模型就像一张表:

Thanks to Zamrony P. Juhara's advice below I was able to successfully create an SQL view and tell my Products model to reference is like a table:

class Product extends Model
{
    protected $table = 'dbo.ProductInfo';
}

现在Product::all()可以正常工作.

关于Peh指出的限制，此应用程序仅用于查看ERP软件中存在的数据，不需要更新或删除任何记录，因此在这种情况下，SQL视图绝对完美.

Regarding the limitations pointed out by Peh this app is purely for viewing the data that exists in the ERP software and won't need to update or delete any records, so in this case the SQL view is absolutely perfect.

推荐答案

您可以将SQL命令转换为VIEW(

You can turn SQL command into VIEW (CREATE VIEW) then use it just like ordinary table in your model.

这篇关于使Laravel模型使用复杂的查询作为表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！