我正在尝试使用laravel查询生成器运行此查询。我认为查询是正确的,因为当我在mysql工作台中运行查询时,查询会执行并得到预期的结果。我知道我们可以用laravel query builder编写原始查询,但它对sql注入漏洞是开放的。所以我试着不带任何疑问地继续。
这是查询
SELECT invoice.InvNo,customer.RouteCode,customer.CustomerCode,rootplan_product.RouteplanCode,invoice.Status
FROM rootplan_product
INNER JOIN
customer ON customer.RouteCode = rootplan_product.RouteCode
AND
customer.CustomerCode = rootplan_product.customercode
INNER JOIN
invoice ON invoice.CustomerCode = customer.CustomerCode
WHERE
rootplan_product.RouteCode='MO-A' AND invoice.Status IN ('PENDING','ACTIVE')
ORDER BY invoice.Status desc
我把每个表都做成了一个模型,并在控制器中使用。因为表名不同于命名约定。我补充说 protected $table = 'correct_table_name';
每种型号都有。
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use \App\Route;
use \App\Customer;
use \App\Invoice;
use \App\Rootplan_Product;
这是控制器功能
public function retrieveRouteCodeData(Request $request){
try {
$RouteCode = $request->RouteCode;
$retrievedData = DB::table('rootplan_product')
->join('customer', function($join){
$join->on('customer.RouteCode', '=', 'rootplan_product.RouteCode');
$join->on(DB::raw('(customer.CustomerCode = rootplan_product.CustomerCode)'));
})
->join('invoice', 'invoice.CustomerCode', '=', 'customer.CustomerCode')
->select('invoice.InvNo', 'customer.RouteCode', 'customer.CustomerCode', 'rootplan_product.RouteplanCode', 'invoice.Status')
->where('rootplan_product.RouteCode', $RouteCode)
->orderBy('invoice.Status','desc')
->get();
return response()->json(['msg'=>'Updated Successfully', 'result'=>$retrievedData, 'success'=>true]);
}
catch (\Exception $e) {
return response()->json(['msg'=>$e->getMessage()]);
}
}
在控制台我得到这个错误
“sqlstate[42s22]:找不到列:1054”“on子句”“中的未知列”“(sql:select)” invoice
. InvNo
, customer
. RouteCode
, customer
. CustomerCode
, rootplan_product
. RouteplanCode
, invoice
. Status
从 rootplan_product
内部连接 customer
在 customer
. RouteCode
= rootplan_product
. RouteCode
和(customer.customercode=rootplan\u product.customercode)=``内部连接 invoice
在 invoice
. CustomerCode
= customer
. CustomerCode
哪里 rootplan_product
. RouteCode
=mo-a订货人 invoice
. Status
描述“
我知道查询是复杂的,任何帮助将不胜感激!
1条答案
按热度按时间piok6c0g1#
问题出在db::raw语句上。请使用以下选项:
这将生成以下sql: