Getting Exception w/ Fluent -> Raw Expressions

Question

I have this query:

$sql = "SELECT 
      po_number AS `po_number`, 
      a.invoice_num AS `invoice_num`, 
      location_name AS `location_name`, 
      state AS `state`,
      terms AS `terms`, 
      duedate AS `duedate`,
      invoice_date AS `invoice_date`,
      sum(qty) AS `qty`,
  CASE WHEN payment_date IS NULL THEN invoice_total ELSE (invoice_total + new_payment_amount) END AS balance
  FROM
  (
  SELECT po_number, invoice_num, payment_date,invoice_total,Location_Job_Id,txn_id,
  terms, duedate,invoice_date,sum(CASE WHEN payment_date > :adate THEN 0 ELSE payment_amount END) AS new_payment_amount FROM PSI_Invoice_Payments a WHERE
  (

  IsPaid = 0 OR
  EXISTS
  (
  SELECT * FROM ATABLE b WHERE
  txn_date > :adate AND a.txn_id = b.txn_id
  )
  )
  AND a.client_id = :aclientid GROUP BY invoice_num
  ) a
  LEFT JOIN BTABLE c ON a.invoice_num = c.invoice_num
  LEFT JOIN CTABLE d ON a.Location_Job_Id = d.cust_location_id
  WHERE a.invoice_date <= :adate GROUP BY a.txn_id ORDER BY a.invoice_num";


    $results = \DB::select( 
        \DB::raw($sql), 
        [':adate' => $adate, ':aclientid' => $aclientid]
        );

I'm getting this Exception:

Illuminate \ Database \ QueryException SQLSTATE[HY093]: Invalid parameter number

Any idea? Is there a better way to execute these kind of queries?

try to change PDO emulation mode to TRUE somewhere in laravel settings. — Your Common Sense, Mar 31 '14 at 19:29
Is it possible that since i'm calling the same parameter [:adate] several times the code has trouble to place it? — rrubiorr81, Mar 31 '14 at 20:03
ok.. any idea about how to solve this... (passing the same parameter several times is frankly ugly) — rrubiorr81, Mar 31 '14 at 20:20
i didn't... i was looking for a way to so (change that mode in PDO), and didn't find where to change this in laravel... (i'll look a bit longer.. not wanting to finish wiht the [http://stackoverflow.com/questions/20670054/invalid-parameter-number-given-with-pdos-prepared-statement] solution) — rrubiorr81, Mar 31 '14 at 20:40

score 2 · Accepted Answer · answered Apr 01 '14 at 10:55

As you can see, emulaton mode is turned off in connector

So, in order to use multiple named placeholders with same name, you have to override this option in app/config/database.php, adding options parameter like this

    'mysql' => array(
        'driver'    => 'mysql',
        'host'      => 'localhost',
        'database'  => 'database',
        'username'  => 'root',
        'password'  => '',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'options'   => array(
            PDO::ATTR_EMULATE_PREPARES => TRUE,
        ),

Getting Exception w/ Fluent -> Raw Expressions

1 Answers1