I have two tables:-
Table 1. Activity
Table 2. ActivityAvailability
Relation Type: HasManyRelation (table 1 HasManyRelation to table2)
I couldn't able use withGraphJoined because it has a limit clause issue with pagination as the objection.js doc says. So I'm using withGraphFetched.
Activity.query().withGraphFetched('activityAvailabilityData').limit(10);
The main difference is that withGraphFetched uses multiple queries under the hood to fetch the result while withGraphJoined uses a single query and joins to fetch the results.
withGraphFetched under the hood queries
Query 1:
select `activity`.`id`, `name`, `type`, `city`, `state`, `address`, `latitude`, `longitude` from `activity` limit ?
Query 2: Here question mark will replace with above query activity IDs.
select `start_age`, `end_age`, `start_date`, `end_date`, `start_time`, `end_time` from `activity_availability` where `activity_availability`.`activity_id` in (?, ?, ?, ?)
Now first problem arries: If I want 10 records each time by limit.I will get if there is no where clause in second query.If incase any where clause is added in the child table (ActivityAvailability),there might be possibility it also eliminates few records from 10 and return.
So I solved it using joins along with withGraphFetched.
Activity.query().withGraphFetched('activityAvailabilityData').join('activity_availability', 'activity.id', '=', 'activity_availability.activity_id')
But this solution also has a drawback.
Whenever any where clause is added in the child table It should also be added in the parent table,just because I used joins.This is becoming difficult to manage. So please let me know if there is another approach?
