We are pleased by the steady growth in use of Synapse by the scientific community, both in terms of number of users and the amount of content in the system. This drives us to make the tools we offer more stable and scalable. In particular the six year old 'entity query' system, which allows you to list entities filtered and sorted by location, dates, and annotations, has been gradually replaced over the past year by the more powerful and stable [File View system](http://docs.synapse.org/articles/fileviews.html). We are pleased to see our user base generally switch over to this new service. Our long term plan is to remove the original entity query service altogether. In the mean time we have taken steps to keep it stable as the queryable content of Synapse continues to grow. For the few who continue to use entity queries, please prepare for the following changes which will be released on May 6, 2017:
* Unbounded query such as `SELECT * FROM entity` will result in 'Bad Request' (400). All queries now must include one of the following: filter by parentId, or projectId, or benefactorId, or select from project. For example, instead of
```
select * from entity order by number_of_samples
```
use one of the following:
```
select * from entity where parentId=="syn123456" order by number_of_samples LIMIT 10 OFFSET 20
select * from entity where projectId=="syn123456" order by number_of_samples LIMIT 10 OFFSET 20
select * from entity where benefactorId=="syn123456" order by number_of_samples LIMIT 10 OFFSET 20
```
* If no limit (page size) is provided, the limit, which used to be very large, will be set to 1000. For example, instead of
```
select * from entity where parentId=="syn123456"
```
iterate through the contents one "page" at a time, like so:
```
`select * from entity where parentId=="syn123456" LIMIT 1000 OFFSET 1`
`select * from entity where parentId=="syn123456" LIMIT 1000 OFFSET 1001`
`select * from entity where parentId=="syn123456" LIMIT 1000 OFFSET 2001`
...
```
* The maximum limit on any query is now 1000. Previously, there was no maximum limit, instead a query would terminate if more than 512000 bytes were read from the database. Again, to update your queries, please add LIMIT and OFFSET as shown above.
* An ORDER BY on an annotation key will always yield an alpha-numeric sort. Previously, an ORDER BY on an annotation key would act as a filter (only entities with the annotation would be returned) but the sort order would match the type of annotation.
* Selecting annotations either explicitly or with SELECT * will only return the first of multiple annotation values for each annotation name. Previously, such selects would return all values of an annotation for each name. Instead do the following:
```
select id from entity where ...
```
and then use `GET /entity/id` (accessible via Python or R using `Synapse.get("syn123456")` or `synGet("syn123456")`, respectively) to retrieve the full metadata for the entity, including all annotations.
* Filtering and selecting by the an entity's alias will no longer work. Please use: `GET/entity/alias/{alias}` instead. This API can be accessed from Python using `Synapse.restPOST("/entity/alias/"+alias, ...)` or from R with `synRestPOST(paste0("/entity/alias/", alias), ...)`
* Selecting or filtering by the entity's activityId will no longer work.
* Query results are "eventually consistent" with the original entities. This means that when an entity is changed there will be a momentary delay before the update appears in query results.
In addition to the file view feature, we provide [a well performing service to simply list the entities in a container](http://docs.synapse.org/rest/POST/entity/children.html).
This can be accessed from Python using `Synapse.restPOST("/entity/children", ...)` or from R with `synRestPOST("/entity/children", ...)`.
For guidance on checking performance of your code against the modified API prior to the release on May 6, please add your inquiry to this thread.
Created by Bruce Hoff brucehoff Type "?synTableQuery" to see the doc's. WGS@values is a data frame containing the query results. aaaah makes sense. Next question: How do you extract the results from WGS? I see that the columns are returned, but the data isn't.
If loadResult=FALSE, then I can extract the entire resulting data frame from the path returned in WGS, but that is cumbersome. Thanks! The entity you gave there (syn9843361) isn't a container (Folder or Project) - so it can't be the parent of anything!
It's a [file view](http://docs.synapse.org/articles/fileviews.html), so the query syntax is different (uses the `synTableQuery` function):
```
WGS = synTableQuery("select * from syn9843361")
```
I am having trouble with the R API. The following command(s) return NULL. Why is this?
WGS=synapseQuery("select * from entity where entity.parentId=='syn9843361'")
WGS=synapseQuery("select * from entity where entity.parentId=='syn9843361' LIMIT 1000")
WGS=synapseQuery("select * from entity where entity.parentId=='syn9843361' LIMIT 1000 OFFSET 1")
Thanks!
@lauragails Yes, you are right. I updated the original post accordingly. Thank you. Thanks for the notice. FYI I tried this and it says that you can't have a zero offset. IE the following works:
```
syn.query("select id, name from entity where entity.parentId=='syn8390071' LIMIT 1000 OFFSET 1")
```
but it doesn't work with an offset of 0.