讓 SimpleDB 支援 paging (offset)

在 implement page 的時候通常會用到 SQL 語法的"offset"
取第一頁:

SELECT * FORM `article` OFFSET LIMIT 10

取第二頁:

SELECT * FORM `article` OFFSET LIMIT 10 OFFSET 10

取第三頁:

SELECT * FORM `article` OFFSET LIMIT 10 OFFSET 20

但 AWS SimpleDB 並不支援 offset 這個與法，必須要用 next_token 方式來取
以下用python boto library做個示例：

sdb_conn = SDBConnection(...)

# Skip first 20 items
query = "SELECT COUNT(*) FORM `article` OFFSET LIMIT 20"
rs = sdb_conn.select(sdb_domain, query=query)

# Get target page
query = "SELECT * FORM `article` OFFSET LIMIT 10"
rs2 = sdb_conn.select(sdb_domain, query=query, next_token=rs.next_token)
for item in rs2:
    ...

"SELECT COUNT(*)" 是最有效率的略過方法，如果用"SELECT * "則會有 return 2500 rows 的限制！


 參考資料

• Chris Moyer: Paging SDB results in boto
• AWS Developer Forums: There is LIMIT, but is there LIMIT 40, ...

SimpleDB - "Too many value tests per predicate in the query expression"

當用Simple下Querey的時候，condition超過20個就會出現下面的錯誤

Too many value tests per predicate in the query expression
或
Client error : Too many value tests per predicate in the query expression.

像這類的語法都不行：

SELECT * FROM domain WHERE id='1' OR id='2' OR ... id='20' OR id='21'

SELECT * FROM domain WHERE id in ('1', '2', ... '20', '21')

SELECT * FROM domain WHERE id in ('1', '2', ... '20') OR id in ('21', ...)

只能考慮分批一次抓20個，然後再合併處理。



2012/05/07 發現一種可以破解20個條件限制的辦法：

SELECT * FROM `domain` WHERE 
  account in ('key1', 'key2', 'key3', 'key4', 'key5', 'key6', 'key7', 'key8', 'key9', 'key10', 
    'key11', 'key12', 'key13', 'key14', 'key15', 'key16', 'key17', 'key18', 'key19', 'key20') OR 
  dummy IS NOT NULL OR 
  account in ('key21', 'key22', 'key23', 'key24', 'key25', 'key26', 'key27', 'key28', 'key29', 'key30', 
    'key31', 'key32', 'key33', 'key34', 'key35', 'key36', 'key37', 'key38', 'key39', 'key40')

沒錯，就是加了"OR dummy IS NOT NULL"這個多餘的條件在中間就可以下達多個條件了！

不過當你有這樣的需求時，就應該想一下是不是設計面有問題，或是該用 relational database了。



參考資料
Amazon SimpleDB » Developer Guide » Amazon SimpleDB Concepts » Limits
Query 101: Building Amazon SimpleDB Queries