balderdashy/waterline-sequel

LIMIT in junctor queries doesn't work correctly

Closed this issue · 7 comments

levity commented

This is the SQL generated when I hit the blueprint route /community/9/users, which is a many-to-many-through association (model code here):

SELECT
  "user"."name",
  "user"."email",
  "user"."id",
  "users_community"."community_id" AS "___community_id" 
FROM "users" AS "user"  
INNER JOIN "users_community" ON "users_community"."users_id" = "user"."id" 
WHERE "user"."id" IN (
  SELECT "users_community"."users_id" 
  FROM "users_community" 
  WHERE "users_community"."community_id" = '9' 
)  LIMIT 30 OFFSET 0

Due to that inner join, if any users are in community 9 and other communities, there will be one row in the result set for each community they're in. The effect of that for my data is that this query which should return 30 distinct users only returns 2, because those 2 users are in 30+ communities.

levity commented

Yes, that sounds like the same issue.

On Monday, December 8, 2014, Thomas Fritz notifications@github.com wrote:

Is this related to this? balderdashy/sails-mysql#155
https://github.com/balderdashy/sails-mysql/issues/155

—
Reply to this email directly or view it on GitHub
#14 (comment)
.

thomasfr commented

Any Updates on this topic? This is really important as it returns too less == wrong results

Benny- commented

You can report if the above pull request fixes this problem for you.

acis commented

any update on this issue? This is very important, as the nested models don't get populated correctly.

I have a many-to-many relationship between Facility and Sport, with 25000 facility instances. This particular Facility has 15 associated sports. This is the sql log for the blueprint Api call:

The first query, for finding the facility is correct, but the second one, to get the sports is

(SELECT
sport.name,
sport.id,
sport.createdAt,
sport.updatedAt,
facility_sports__sport_facilities.facility_sports AS '___facility_sports'
FROM
sport AS sport
INNER JOIN
facility_sports__sport_facilities ON facility_sports__sport_facilities.sport_facilities = sport.id
WHERE
sport.id IN (SELECT
facility_sports__sport_facilities.sport_facilities
FROM
facility_sports__sport_facilities
WHERE
facility_sports__sport_facilities.facility_sports = 50462)
ORDER BY sport.id ASC
LIMIT 7000)
The Outer query is not filtered on the facility id. Adding the

facility_sports__sport_facilities.facility_sports = 50462)
condition to the outer where as well returns the correct result, 15 entries.

The current query has 9610(!!!!) rows, is slow and setting the waterline limit config to 10000 is undesirable.

devinivy commented

This should be fixed as of waterline-sequel 0.5.0, which is used in sails-mysql 0.11.0. Closing this– we can reopen if the issue persists.