How do I achieve the result of doing beginsWith on 2 different attributes in a single query?

To achieve the result of doing beginsWith on two different attributes in a single query, you can use a composite attribute approach in Amazon SimpleDB or create a composite key in Amazon DynamoDB. Here's how you can accomplish this:

1. Create a composite attribute: Combine the accountHierarchyPath and pageHierarchyPath into a single attribute. For example, you could create a new attribute called "hierarchyPath" that concatenates both paths:

hierarchyPath = accountHierarchyPath + "#" + pageHierarchyPath

So, for your example, it would look like: hierarchyPath: account1#account2#account3#folder1#folder2#folder3

2. Query using the composite attribute: Now you can perform a single beginsWith query on this composite attribute. Your query would look something like this:

select * from myDomain where hierarchyPath beginswith 'account1#' and hierarchyPath like '%#folder1#folder2#%'

This query will return all items where the accountHierarchyPath starts with "account1" and the pageHierarchyPath includes "folder1#folder2" at any position.

By using this composite attribute approach, you can effectively perform a beginsWith operation on both attributes in a single query. This method is more efficient than using multiple predicates and allows you to search for patterns across both hierarchies simultaneously.

Remember to use appropriate separators (like '#') when combining variable-length fields to ensure accurate querying. Also, make sure to update the composite attribute whenever either of the original attributes changes to keep the data consistent.
Sources
Tuning Your Queries Using Composite Attributes - Amazon SimpleDB