-1

I have a user table and a location table. A user can visit many locations and locations can be visited by many users.

I want to return results, ordered by distance, of the nearest locations that a user has not visited.

So many problems arise here. Join tables cannot be used, as results will return a maximum of 1000 rows. This means that it is not possible to do a select on the Locations and join tables and guarantee all matching results are excluded. i.e. I cannot use whereNotContainedIn due to the 1000 row limit.

I cannot use Relations as they also suffer the same 1000 row limit, so cannot guarantee accurate results.

I could use Pointers or an Array. Pointers are meant for one to one relationships, so there is no simple way to append to a list of pointers. Arrays are memory heavy and not meant to spill over 100 entries ideally, so Im not sure which is the best option.

Has anyone had to make this kind of query before?

RonnyKnoxville
  • 6,166
  • 10
  • 46
  • 75

1 Answers1

0

You can create another table (say user_locations), which includes user_id and location_id as foreign keys. This will remove many-to-many relations.

http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php
There is an example here, (OrderLine table).

  • This is exactly what I was saying I cannot do, as there is a hard limit in Parse of 1000 rows. It is not possible to get all of the locations where the user has not been, as if the user has been to over 1000 locations then not all of the results will be returned – RonnyKnoxville Aug 27 '15 at 14:44
  • @JackalopeZero - The query (for this idea or for a relation) implements skip. The common approach for getting > 1K rows is to run the query iteratively, with skip set to the count of objects received on the last run. – danh Aug 27 '15 at 14:50
  • It is best practice to never loop queries. A: This will still limit to 10k results in total and B: This is incredibly inefficient to have to query every single row. Although `notContainedIn` is essentially doing that anyway – RonnyKnoxville Aug 27 '15 at 14:56
  • Also @danh could you please point me to any official or unofficial documentation stating that it is common approach to loop the entire table? – RonnyKnoxville Aug 27 '15 at 14:58
  • @JackalopeZero - "common" was a poor choice of wording on my part. The right word is "only". It is the "only approach". You're already aware of the documented evidence for this, specifically, the documented max limit of 1K. "Iterative" was also a poor word choice. "Over and over" would have been better, the way a user paging through thousands or tens of thousands of results might need them. – danh Aug 27 '15 at 15:07
  • So your design is correct, i thought you are trying to improve performance. Where that 1000 rows limit is coming from ? I searched and mysql has an `optional` limit, but could be removed. –  Aug 27 '15 at 15:14
  • @ömeryılmaz - see the parse.com tag. It is a non-sql back-end as a service with some under-explained, occasionally unfortunate resource limitations. It has a "relation" type that provides something like a join table functionally. – danh Aug 27 '15 at 15:18
  • Haha I like the corrections @danh. Well, I've had a chat with the team about this and considering my research (this is the 3rd day trying to solve the problem) and the lack of scalability from Parse, we will eventually be moving platform. For anyone viewing this question, danh is correct, there is no good answer. – RonnyKnoxville Aug 27 '15 at 15:23
  • I faced a similar problem and ended up migrating my application from Parse to AWS and used MongoDB. – Halfpint Aug 27 '15 at 15:26