Mixing
Rails - Has many, with query get all nested and parent object
I have been working on an scheduling app, and running into an issue with finding a way to efficiently get specific records.
Here are the models in question.
class Task < ApplicationRecord
belongs_to :user, optional: true
belongs_to :project, optional: true
belongs_to :project_schedule, optional: true
belongs_to :user_schedule, optional: true
class User < ApplicationRecord
include Filterable
validates :email, presence: true
validates :email, uniqueness: true
has_many :tasks
What the front end expects is a json of all users, with all tasks nested within them. This has become too cumbersome to load that many tasks and I was attempting to specify a date range to the tasks while still getting all users.
So this seems more difficult that I originally expected. I can get all users efficiently, all tasks within a date range, and all users with tasks within a given date range with the tasks included. But getting all users with the tasks nested within a given date range has evaded me.
What I've tried
User.filter(params.slice(:project_ids, :email, :id))
.includes(:tasks).where('((tasks.end_date BETWEEN ? AND ?)
OR (tasks.start_date BETWEEN ? AND ?))',
week_start, week_end, week_start, week_end)
.references(:tasks).order(:name)
I've also tried merging all users and all users with tasks within the range to no avail.
I feel like key is raw SQL but I couldn't wrap my head around the query yesterday.
Any insight would be helpful and appreciated.
sql ruby-on-rails ruby activerecord
asked Nov 21 '18 at 17:49
Dale PalmerDale Palmer
288
add a comment |
I have been working on an scheduling app, and running into an issue with finding a way to efficiently get specific records.
Here are the models in question.
class Task < ApplicationRecord
belongs_to :user, optional: true
belongs_to :project, optional: true
belongs_to :project_schedule, optional: true
belongs_to :user_schedule, optional: true
class User < ApplicationRecord
include Filterable
validates :email, presence: true
validates :email, uniqueness: true
has_many :tasks
What the front end expects is a json of all users, with all tasks nested within them. This has become too cumbersome to load that many tasks and I was attempting to specify a date range to the tasks while still getting all users.
So this seems more difficult that I originally expected. I can get all users efficiently, all tasks within a date range, and all users with tasks within a given date range with the tasks included. But getting all users with the tasks nested within a given date range has evaded me.
What I've tried
User.filter(params.slice(:project_ids, :email, :id))
.includes(:tasks).where('((tasks.end_date BETWEEN ? AND ?)
OR (tasks.start_date BETWEEN ? AND ?))',
week_start, week_end, week_start, week_end)
.references(:tasks).order(:name)
I've also tried merging all users and all users with tasks within the range to no avail.
I feel like key is raw SQL but I couldn't wrap my head around the query yesterday.
Any insight would be helpful and appreciated.
sql ruby-on-rails ruby activerecord
asked Nov 21 '18 at 17:49
Dale PalmerDale Palmer
288
2
What is the issue with that query? Is your goal "All Users" and just load the tasks in a given range? (Outer Join with condition) If so maybe just execute 2 queries (one for users and one for tasks in the date range) then just unify them in the UI. What rails version are you using as well as rails 5 offersleft_joinsas well asorsyntax options?
– engineersmnky
Nov 21 '18 at 17:57
The issue with the query is I only get the users with tasks within the date range, when my UI expects a list of all users. As for version, I am using rails 5. As for unifying them on the UI that's what I am currently trying. I thought it would prove difficult to line up the tasks with the correct user, when I first considered this route.
– Dale Palmer
Nov 21 '18 at 18:36
add a comment |
I have been working on an scheduling app, and running into an issue with finding a way to efficiently get specific records.
Here are the models in question.
class Task < ApplicationRecord
belongs_to :user, optional: true
belongs_to :project, optional: true
belongs_to :project_schedule, optional: true
belongs_to :user_schedule, optional: true
class User < ApplicationRecord
include Filterable
validates :email, presence: true
validates :email, uniqueness: true
has_many :tasks
What the front end expects is a json of all users, with all tasks nested within them. This has become too cumbersome to load that many tasks and I was attempting to specify a date range to the tasks while still getting all users.
So this seems more difficult that I originally expected. I can get all users efficiently, all tasks within a date range, and all users with tasks within a given date range with the tasks included. But getting all users with the tasks nested within a given date range has evaded me.
What I've tried
User.filter(params.slice(:project_ids, :email, :id))
.includes(:tasks).where('((tasks.end_date BETWEEN ? AND ?)
OR (tasks.start_date BETWEEN ? AND ?))',
week_start, week_end, week_start, week_end)
.references(:tasks).order(:name)
I've also tried merging all users and all users with tasks within the range to no avail.
I feel like key is raw SQL but I couldn't wrap my head around the query yesterday.
Any insight would be helpful and appreciated.
sql ruby-on-rails ruby activerecord
asked Nov 21 '18 at 17:49
Dale PalmerDale Palmer
288
I have been working on an scheduling app, and running into an issue with finding a way to efficiently get specific records.
Here are the models in question.
class Task < ApplicationRecord
belongs_to :user, optional: true
belongs_to :project, optional: true
belongs_to :project_schedule, optional: true
belongs_to :user_schedule, optional: true
class User < ApplicationRecord
include Filterable
validates :email, presence: true
validates :email, uniqueness: true
has_many :tasks
What the front end expects is a json of all users, with all tasks nested within them. This has become too cumbersome to load that many tasks and I was attempting to specify a date range to the tasks while still getting all users.
So this seems more difficult that I originally expected. I can get all users efficiently, all tasks within a date range, and all users with tasks within a given date range with the tasks included. But getting all users with the tasks nested within a given date range has evaded me.
What I've tried
User.filter(params.slice(:project_ids, :email, :id))
.includes(:tasks).where('((tasks.end_date BETWEEN ? AND ?)
OR (tasks.start_date BETWEEN ? AND ?))',
week_start, week_end, week_start, week_end)
.references(:tasks).order(:name)
I've also tried merging all users and all users with tasks within the range to no avail.
I feel like key is raw SQL but I couldn't wrap my head around the query yesterday.
Any insight would be helpful and appreciated.
sql ruby-on-rails ruby activerecord
sql ruby-on-rails ruby activerecord
asked Nov 21 '18 at 17:49
Dale PalmerDale Palmer
288
asked Nov 21 '18 at 17:49
Dale PalmerDale Palmer
288
asked Nov 21 '18 at 17:49
Dale PalmerDale Palmer
288
asked Nov 21 '18 at 17:49
Dale PalmerDale Palmer
288
asked Nov 21 '18 at 17:49
Dale PalmerDale Palmer
288
288
2
What is the issue with that query? Is your goal "All Users" and just load the tasks in a given range? (Outer Join with condition) If so maybe just execute 2 queries (one for users and one for tasks in the date range) then just unify them in the UI. What rails version are you using as well as rails 5 offersleft_joinsas well asorsyntax options?
– engineersmnky
Nov 21 '18 at 17:57
The issue with the query is I only get the users with tasks within the date range, when my UI expects a list of all users. As for version, I am using rails 5. As for unifying them on the UI that's what I am currently trying. I thought it would prove difficult to line up the tasks with the correct user, when I first considered this route.
– Dale Palmer
Nov 21 '18 at 18:36
add a comment |
2
What is the issue with that query? Is your goal "All Users" and just load the tasks in a given range? (Outer Join with condition) If so maybe just execute 2 queries (one for users and one for tasks in the date range) then just unify them in the UI. What rails version are you using as well as rails 5 offersleft_joinsas well asorsyntax options?
– engineersmnky
Nov 21 '18 at 17:57
The issue with the query is I only get the users with tasks within the date range, when my UI expects a list of all users. As for version, I am using rails 5. As for unifying them on the UI that's what I am currently trying. I thought it would prove difficult to line up the tasks with the correct user, when I first considered this route.
– Dale Palmer
Nov 21 '18 at 18:36
2
2
What is the issue with that query? Is your goal "All Users" and just load the tasks in a given range? (Outer Join with condition) If so maybe just execute 2 queries (one for users and one for tasks in the date range) then just unify them in the UI. What rails version are you using as well as rails 5 offers
left_joins as well as or syntax options?– engineersmnky
Nov 21 '18 at 17:57
What is the issue with that query? Is your goal "All Users" and just load the tasks in a given range? (Outer Join with condition) If so maybe just execute 2 queries (one for users and one for tasks in the date range) then just unify them in the UI. What rails version are you using as well as rails 5 offers
left_joins as well as or syntax options?– engineersmnky
Nov 21 '18 at 17:57
The issue with the query is I only get the users with tasks within the date range, when my UI expects a list of all users. As for version, I am using rails 5. As for unifying them on the UI that's what I am currently trying. I thought it would prove difficult to line up the tasks with the correct user, when I first considered this route.
– Dale Palmer
Nov 21 '18 at 18:36
The issue with the query is I only get the users with tasks within the date range, when my UI expects a list of all users. As for version, I am using rails 5. As for unifying them on the UI that's what I am currently trying. I thought it would prove difficult to line up the tasks with the correct user, when I first considered this route.
– Dale Palmer
Nov 21 '18 at 18:36
add a comment |
1 Answer
1
active
oldest
votes
What you need is a left outer join. However, if you have a WHERE clause, this will filter away results for which the join fails, see Left Join With Where Clause.
You need to specify the condition as part of the JOIN clause, so something along these lines:
User.filter(params.slice(:project_ids, :email, :id))
.joins("LEFT OUTER JOINS tasks ON tasks.user_id = users.id AND
tasks.end_date BETWEEN :week_start AND :week_end OR
tasks.start_date BETWEEN :week_start AND
:week_end", week_start: week_start, week_end: week_end
.includes(:tasks).order(:name)
answered Nov 22 '18 at 11:23
Stefan RendevskiStefan Rendevski
515
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417899%2frails-has-many-with-query-get-all-nested-and-parent-object%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
What you need is a left outer join. However, if you have a WHERE clause, this will filter away results for which the join fails, see Left Join With Where Clause.
You need to specify the condition as part of the JOIN clause, so something along these lines:
User.filter(params.slice(:project_ids, :email, :id))
.joins("LEFT OUTER JOINS tasks ON tasks.user_id = users.id AND
tasks.end_date BETWEEN :week_start AND :week_end OR
tasks.start_date BETWEEN :week_start AND
:week_end", week_start: week_start, week_end: week_end
.includes(:tasks).order(:name)
answered Nov 22 '18 at 11:23
Stefan RendevskiStefan Rendevski
515
add a comment |
What you need is a left outer join. However, if you have a WHERE clause, this will filter away results for which the join fails, see Left Join With Where Clause.
You need to specify the condition as part of the JOIN clause, so something along these lines:
User.filter(params.slice(:project_ids, :email, :id))
.joins("LEFT OUTER JOINS tasks ON tasks.user_id = users.id AND
tasks.end_date BETWEEN :week_start AND :week_end OR
tasks.start_date BETWEEN :week_start AND
:week_end", week_start: week_start, week_end: week_end
.includes(:tasks).order(:name)
answered Nov 22 '18 at 11:23
Stefan RendevskiStefan Rendevski
515
add a comment |
What you need is a left outer join. However, if you have a WHERE clause, this will filter away results for which the join fails, see Left Join With Where Clause.
You need to specify the condition as part of the JOIN clause, so something along these lines:
User.filter(params.slice(:project_ids, :email, :id))
.joins("LEFT OUTER JOINS tasks ON tasks.user_id = users.id AND
tasks.end_date BETWEEN :week_start AND :week_end OR
tasks.start_date BETWEEN :week_start AND
:week_end", week_start: week_start, week_end: week_end
.includes(:tasks).order(:name)
answered Nov 22 '18 at 11:23
Stefan RendevskiStefan Rendevski
515
What you need is a left outer join. However, if you have a WHERE clause, this will filter away results for which the join fails, see Left Join With Where Clause.
You need to specify the condition as part of the JOIN clause, so something along these lines:
User.filter(params.slice(:project_ids, :email, :id))
.joins("LEFT OUTER JOINS tasks ON tasks.user_id = users.id AND
tasks.end_date BETWEEN :week_start AND :week_end OR
tasks.start_date BETWEEN :week_start AND
:week_end", week_start: week_start, week_end: week_end
.includes(:tasks).order(:name)
answered Nov 22 '18 at 11:23
Stefan RendevskiStefan Rendevski
515
answered Nov 22 '18 at 11:23
Stefan RendevskiStefan Rendevski
515
answered Nov 22 '18 at 11:23
Stefan RendevskiStefan Rendevski
515
answered Nov 22 '18 at 11:23
Stefan RendevskiStefan Rendevski
515
515
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417899%2frails-has-many-with-query-get-all-nested-and-parent-object%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
What is the issue with that query? Is your goal "All Users" and just load the tasks in a given range? (Outer Join with condition) If so maybe just execute 2 queries (one for users and one for tasks in the date range) then just unify them in the UI. What rails version are you using as well as rails 5 offers
left_joinsas well asorsyntax options?– engineersmnky
Nov 21 '18 at 17:57
The issue with the query is I only get the users with tasks within the date range, when my UI expects a list of all users. As for version, I am using rails 5. As for unifying them on the UI that's what I am currently trying. I thought it would prove difficult to line up the tasks with the correct user, when I first considered this route.
– Dale Palmer
Nov 21 '18 at 18:36