Complex MySQL inner join query with Laravel Query Builder

Question

I'm trying to run this query with Laravel Query Builder. I think query is correct because when I run the query in the MySQL Workbench the query executes and I get the expected results. I know that we can write a raw query with laravel query builder but it's open to SQL injection vulnerabilities. So I'm trying to go ahead without raw queries.

This is the query

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

I have made each table a Model and use in the controller like this. Since the table names are different from the naming conventions. I have added protected $table = 'correct_table_name'; in every model.

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;

This is the controller function

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()]);

      }


}

In the console I get this error

"SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'on clause' (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 order by invoice.Status desc)"

I understand the query is complex and any help would be appreciated !

To me it looks like your DB::raw join is causing problems, did you try doing it like the join on the line above? — SplittyDev
– SplittyDev, Commented Dec 19, 2018 at 12:20
@SplittyDev Yes.. I tried but I couldn't figure out inner join with AND part in the query. So I tried this way — Kasun
– Kasun, Commented Dec 19, 2018 at 12:22
I believe you can do the following: $join->on('customer.RouteCode', '=', 'rootplan_product.RouteCode')->where('customer.CustomerCode', '=', 'rootplan_product.CustomerCode'); — SplittyDev
– SplittyDev, Commented Dec 19, 2018 at 12:24

Sumit Kumar · Accepted Answer · 2018-12-19 12:38:38Z

The issue was with the DB::raw statement. Please use the following:

DB::table('rootplan_product')->join('customer', function ($join) {
    $join->on('customer.RouteCode', '=', 'rootplan_product.RouteCode');
    $join->on('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', '123')
    ->whereIn('invoice.Status', ['PENDING','ACTIVE'])
    ->orderBy('invoice.Status', 'desc')
    ->get();

This will generate the following SQL:

SELECT `invoice`.`InvNo`,
       `customer`.`RouteCode`,
       `customer`.`CustomerCode`,
       `rootplan_product`.`RouteplanCode`,
       `invoice`.`Status`
FROM `test`
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` = ?
  AND `invoice`.`Status` IN (?, ?)
ORDER BY `invoice`.`Status` DESC

Collectives™ on Stack Overflow

Complex MySQL inner join query with Laravel Query Builder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related