0

How do i create this SQL statement with cakephp 3;

 delete FROM bookmarks WHERE substring(url , 4) != 'http'

Ive tried;

$this->Bookmark->deleteAll('substring(Bookmark.url,4) !=' => 'http',false);

Cakephp doesnt like the Substring or LEFT() function

fuzzy dunlop
  • 477
  • 2
  • 10
  • 22
  • "_doesn't like_" is not a proper problem description! Despite maybe being answerable as it stands, please always be as specific as possible as to what _exactly_ happens, and what you'd expect to happen instead. – ndm Apr 09 '16 at 00:37
  • Also, probably kind of a duplicate of **http://stackoverflow.com/questions/30845997/how-to-genereate-sql-function-calls-with-the-cakephp-query-builder**. – ndm Apr 09 '16 at 00:50

1 Answers1

0

How about this:

$this->Bookmark->deleteAll([
    'NOT' => ['SUBSTRING(url, 1, 4)' => 'http'],
]);
Dave
  • 28,833
  • 23
  • 113
  • 183
  • This will generate invalid SQL, as in `DELETE` queries the condition key is subject to alias stripping, which basically works like "_give me everything after the possible dot_", so that you'll end up with something like `WHERE NOT (url, 4) = 'http')`. It will work without alias, but using an expression that would support auto quoting and dialect transforming might be the better/more portable choice (yeah I know, `SUBSTRING` isn't yet subject to transforming). On a side note, it needs to be `1, 4`, not just `4`. – ndm Apr 09 '16 at 10:18
  • yes it made a bad query not sure how to fix it. It seems to ignore substring SQL function. output; DELETE FROM bookmarks WHERE NOT (url, 4) = 'http' – fuzzy dunlop Apr 09 '16 at 13:15