usmanhalalit / pixie

Database query builder for PHP, framework agnostic, lightweight and expressive.
MIT License
672 stars 180 forks source link

support parameterized raw expressions in inserts/updates/criteria values #195

Open drdaxxy opened 6 years ago

drdaxxy commented 6 years ago

This PR enables using raw() expressions with parameters in insert queries, update queries and inside (right-hand side) value expressions of (where) criteria. Tests updated accordingly.

Insert

QB::table('people')->getQuery('insert', [
    'firstname' => 'Jane',
    'lastname'  => QB::raw('UPPER(?)', 'Doe'),
    'age'       => 27
])->getRawSql();

// before: INSERT INTO `people` (`firstname`,`lastname`,`age`) VALUES ('Jane',UPPER(27),?)
// after:  INSERT INTO `people` (`firstname`,`lastname`,`age`) VALUES ('Jane',UPPER('Doe'),27)

Where

QB::table('people')
    ->where('firstname', '=', 'Jane')
    ->where('lastname', '=', QB::raw('UPPER(?)', 'Doe'))
    ->getQuery()->getRawSql();

// before: SELECT * FROM `people` WHERE `firstname` = 'Jane' AND `lastname` = UPPER(?)
// after:  SELECT * FROM `people` WHERE `firstname` = 'Jane' AND `lastname` = UPPER('Doe')
TCB13 commented 6 years ago

Hi @drdaxxy many thanks for the PR and effort, this is definitely a great feature. Can you review @usmanhalalit ?