Description
Hi, I found a bug about order() method.
I write a query ordered by "last_motion_photo_id" and print the sql(#1) via query.asSQL(), it is like:
SELECT "sites"."_id", "sites"."site_admin", "sites"."description", "sites"."timezone", "sites"."address1", "sites"."address2", "sites"."city", "sites"."state", "sites"."zip", "sites"."community", "sites"."status", "sites"."last_photo_id", "sites"."visible", "sites"."sort_order", "photos"."date_taken", "sites"."last_motion_photo_id", "sites"."last_motion_datetime" FROM "sites" LEFT OUTER JOIN "photos" ON ("photos"."_id" = "sites"."last_photo_id") WHERE ((("sites"."active" = 1) AND ("sites"."visible" = 1)) AND ("sites"."deleted" = 0)) ORDER BY "last_motion_photo_id" DESC
I can get the result.
Then I change the query order by "_id" and print it(#2), it is like:
SELECT "sites"."_id", "sites"."site_admin", "sites"."description", "sites"."timezone", "sites"."address1", "sites"."address2", "sites"."city", "sites"."state", "sites"."zip", "sites"."community", "sites"."status", "sites"."last_photo_id", "sites"."visible", "sites"."sort_order", "photos"."date_taken", "sites"."last_motion_photo_id", "sites"."last_motion_datetime" FROM "sites" LEFT OUTER JOIN "photos" ON ("photos"."_id" = "sites"."last_photo_id") WHERE ((("sites"."active" = 1) AND ("sites"."visible" = 1)) AND ("sites"."deleted" = 0)) ORDER BY "_id"
I got nothing.
Then I think maybe because the _id in sites table is same with the photos table, so I change the "_id" to "site_id", and print it:
SELECT "sites"."site_id", "sites"."site_admin", "sites"."description", "sites"."timezone", "sites"."address1", "sites"."address2", "sites"."city", "sites"."state", "sites"."zip", "sites"."community", "sites"."status", "sites"."last_photo_id", "sites"."visible", "sites"."sort_order", "photos"."date_taken", "sites"."last_motion_photo_id", "sites"."last_motion_datetime" FROM "sites" LEFT OUTER JOIN "photos" ON ("photos"."_id" = "sites"."last_photo_id") WHERE ((("sites"."active" = 1) AND ("sites"."visible" = 1)) AND ("sites"."deleted" = 0)) ORDER BY "site_id" DESC
Still got nothing.
Then I change the db.prepare(QueryType) to db.prepare(statement) and pass in a sql string(#2), it is like:
"SELECT \"sites\".\"_id\", \"sites\".\"site_admin\", \"sites\".\"description\", \"sites\".\"timezone\", \"sites\".\"address1\", \"sites\".\"address2\", \"sites\".\"city\", \"sites\".\"state\", \"sites\".\"zip\", \"sites\".\"community\", \"sites\".\"status\", \"sites\".\"last_photo_id\", \"sites\".\"visible\", \"sites\".\"sort_order\", \"photos\".\"date_taken\", \"sites\".\"last_motion_photo_id\", \"sites\".\"last_motion_datetime\" FROM \"sites\" LEFT OUTER JOIN \"photos\" ON (\"photos\".\"_id\" = \"sites\".\"last_photo_id\") WHERE (((\"sites\".\"active\" = 1) AND (\"sites\".\"visible\" = 1)) AND (\"sites\".\"deleted\" = 0)) ORDER BY \"_id\" DESC"
I still got nothing.
But after I change the sql to:
"SELECT \"sites\".\"_id\", \"sites\".\"site_admin\", \"sites\".\"description\", \"sites\".\"timezone\", \"sites\".\"address1\", \"sites\".\"address2\", \"sites\".\"city\", \"sites\".\"state\", \"sites\".\"zip\", \"sites\".\"community\", \"sites\".\"status\", \"sites\".\"last_photo_id\", \"sites\".\"visible\", \"sites\".\"sort_order\", \"photos\".\"date_taken\", \"sites\".\"last_motion_photo_id\", \"sites\".\"last_motion_datetime\" FROM \"sites\" LEFT OUTER JOIN \"photos\" ON (\"photos\".\"_id\" = \"sites\".\"last_photo_id\") WHERE (((\"sites\".\"active\" = 1) AND (\"sites\".\"visible\" = 1)) AND (\"sites\".\"deleted\" = 0)) ORDER BY \"sites\".\"_id\" DESC"
I got the result I want.
I don't know what is wrong with it, but when we create an Expressible, like "siteID.desc", maybe we can add the table name somehow, so it will be parsed like ""sites"."_id" DESC"", then the bug will go.