How to prevent model from putting quotes around everything in sql


#1

Hi, I am trying to run a complex query within a model. When I try to do a select on the following,

CONCAT(LPAD(“DELIVERY_REQUESTS_ID”, 12, ‘0’), LPAD(“DELIVERY_REQUEST_ITEM”, 4, ‘0’))

it keeps trying to convert it to:

CONCAT(LPAD(“DELIVERY_REQUESTS_ID”,“12”,"‘0’)", LPAD(“DELIVERY_REQUEST_ITEM”,“4”, ‘0’))

which is really annoying and won’t work; notice the double quotes surrounding the first ‘0’) . Why is it doing that and how can I get that to stop? Thank you,

Garrett


#2

Nevermind, adding parethesis around the integers such as (4), (12), etc worked.


#3

Hard to say, how do you generate that query?

See http://dsql.readthedocs.io/en/develop/expressions.html?highlight=escape#escaping-methods


#4

Please show us your code fragment where you generate that expression !


#5

Here is my code which now works, with the numeric values in parenthesis. It was trying to add double-quotes around numbers I had already escaped, such as ‘0’, so I changed it to (‘0’), etc.

$m = new \atk4\data\Model($db, [’(SELECT “ITEMNUMBER”, “LATEST_STATUS”, “DATETIMESTAMP” FROM (SELECT ALIAS_1.*, MAX(DATETIMESTAMP) OVER (PARTITION BY “ITEMNUMBER”) AS MAX_DATETIMESTAMP FROM (SELECT DISTINCT “ITEMNUMBER”, “STATUS” “LATEST_STATUS”, TO_TIMESTAMP(CONCAT(“DATESTAMP”, “TIMESTAMP”), ‘MM/DD/YYYYHH24:MI:SS’) “DATETIMESTAMP” FROM “DELIVERY_REQUEST_BARCODES” UNION SELECT (CONCAT(LPAD(“DELIVERY_REQUESTS_ID”, (12), (‘0’)), LPAD(“DELIVERY_REQUEST_ITEM”, (4), (‘0’)))) “ITEMNUMBER”, “STATUS”, “DELIVERY_REQUEST_ITEM_STAT”.“MODIFIED_ON” FROM “DELIVERY_REQUEST_ITEM_STAT” INNER JOIN “DELIVERY_REQUEST_ITEMS” ON “DELIVERY_REQUEST_ITEMS”.“ID” = “DELIVERY_REQUEST_ITEM_STAT”.“DELIVERY_REQUEST_ITEMS_ID” WHERE “STATUS” IS NOT NULL) ALIAS_1) ALIAS_2 WHERE DATETIMESTAMP = MAX_DATETIMESTAMP)’, ‘id_field’=>‘ITEMNUMBER’]);


#6

Interesting use-case. Basically you’re setting all this query as Model->$table, right?
Most of the time it’s not how to use data models, but in this particular case maybe it’s useful for some kind of report or something. If you would like to create such query by using UnionModel and customize query templates for over partition by, then probably code will become much more complex than it is now.

Speaking about escaping numeric values:

  1. i don’t see any reason why should you escape them with apostrophes at all. I mean ‘0’.
  2. using unescaped apostrophe in string which is marked with ’ surely will generate syntax error, because it will break string. You can use ‘0’ there if you really need that.
  3. also see ‘MM/DD/YYYYHH24:MI:SS’ part. It’s also wrapped in unescaped apostrophes i guess.

#7

I don’t define $table anywhere for this model. But, I do apply addField and addCondition to it later. All the escape (\) marks got unescaped in my post. LPAD() in Oracle uses a string as its third argument, so I put it in quotes.


#8

You set all this query as Model->table because of this: https://github.com/atk4/data/blob/develop/src/Model.php#L296

And this line is which consumes and soft-escapes your long query string as table in SELECT https://github.com/atk4/dsql/blob/develop/src/Query.php#L308

Try setting it as expression not string. Something like this:
$m = new \atk4\data\Model($db, [new \atk4\dsql\Expression(‘your long sub-select query goes here’)]);


#9

that’s a curious way to define sub-select for model, but not unexpected. It’s how it is planned. You may want to wrap it into new Expression() if you want it to be left as-is, table, i believe, is otherwise escaped.