0

NOTE: using Lravel 4.1 framework

Hi My Sql query for POSTGRES DB is

$results1 = DB::table('vehicle_brands')
                    ->where('vehicle_brands.caption','ilike', '%'.$_GET['key_word'].'%')
                    ->join('vehicle_manufacturers','vehicle_manufacturers.id', '=', 'vehicle_brands.vehicle_manufacturer_id')
                    ->select('vehicle_brands.*','vehicle_manufacturers.caption AS vm_caption');
                    $results = DB::table('vehicle_brands')
                    ->where('vehicle_manufacturers.caption','ilike', '%'.$_GET['key_word'].'%')
                    ->join('vehicle_manufacturers','vehicle_manufacturers.id', '=', 'vehicle_brands.vehicle_manufacturer_id')
                    ->select('vehicle_brands.*','vehicle_manufacturers.caption AS vm_caption')
                    ->union($results1)
                    ->orderBy('vehicle_brands.caption','ASC')
                    ->get();
                return Response::json(array('status' => true,'data' => $results));

The error i am getting is as follows:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "union" LINE 1: ... ilike $1 order by "vehicle_brands"."caption" asc union sele... ^ (SQL: select "vehicle_brands"., "vehicle_manufacturers"."caption" as "vm_caption" from "vehicle_brands" inner join "vehicle_manufacturers" on "vehicle_manufacturers"."id" = "vehicle_brands"."vehicle_manufacturer_id" where "vehicle_manufacturers"."caption" ilike %o% order by "vehicle_brands"."caption" asc union select "vehicle_brands"., "vehicle_manufacturers"."caption" as "vm_caption" from "vehicle_brands" inner join "vehicle_manufacturers" on "vehicle_manufacturers"."id" = "vehicle_brands"."vehicle_manufacturer_id" where "vehicle_brands"."caption" ilike %o%)

What am i doing wrong in the query?

Thankyou.

Ekancepts
  • 71
  • 1
  • 6
  • 3
    Please show us the generated SQL. What programming language is that? Which framework are you using to generate the SQL? –  Nov 01 '14 at 10:45

1 Answers1

0

I am using Laravel 4.1 as a framework and postgres as the DB.

I researched further after posting the above question and came to a solution for my question it is as follows.

It is a bug the discussion can be seen at https://github.com/laravel/framework/pull/3901 the discussion also provides a solution too.

Found a Stackoverflow solution too(This is same as above but with clarity)

https://stackoverflow.com/a/25722206

I coverted my code to as below :

$query1 = DB::table('vehicle_brands')
            ->where('vehicle_brands.caption','ilike', '%'.$term.'%')
            ->join('vehicle_manufacturers','vehicle_manufacturers.id', '=', 'vehicle_brands.vehicle_manufacturer_id')
            ->select('vehicle_brands.*','vehicle_manufacturers.caption AS vm_caption');
        $query2 = DB::table('vehicle_brands')
            ->where('vehicle_manufacturers.caption','ilike', '%'.$term.'%')
            ->join('vehicle_manufacturers','vehicle_manufacturers.id', '=', 'vehicle_brands.vehicle_manufacturer_id')
            ->select('vehicle_brands.*','vehicle_manufacturers.caption AS vm_caption');
        $query = $query1->union($query2);
$items = DB::table(DB::raw("($querySql order by 'caption' 'ASC') as a"))->mergeBindings($query)
                        ->take($limit)
                        ->skip($limit * ($page - 1))
                        ->get();

The $term is the search keyword.

Thank You.

Community
  • 1
  • 1
Ekancepts
  • 71
  • 1
  • 6