How to create a scope for records with highest value in associated table column
0
I have four related models:
class Quest < ApplicationRecord
has_many :steps, -> { order(step_number: :asc) }
def last_step_number
steps.maximum(:step_number)
end
end
class Step < ApplicationRecord
validates :step_number, presence: true
belongs_to :quest
end
class Teamquest < ApplicationRecord
validates :last_step_completed, presence: true
belongs_to :quest
def is_all_steps_completed
last_step_completed == quest.last_step_number
end
end
I would like to add a scope to Teamquest that returns all records where Teamquest model method is_all_steps_completed == true
sql postgresql activerecord ruby-on-rails-5
asked Nov 15 '18 at 18:37
Elijah HallElijah Hall
306
add a comment |
0
I have four related models:
class Quest < ApplicationRecord
has_many :steps, -> { order(step_number: :asc) }
def last_step_number
steps.maximum(:step_number)
end
end
class Step < ApplicationRecord
validates :step_number, presence: true
belongs_to :quest
end
class Teamquest < ApplicationRecord
validates :last_step_completed, presence: true
belongs_to :quest
def is_all_steps_completed
last_step_completed == quest.last_step_number
end
end
I would like to add a scope to Teamquest that returns all records where Teamquest model method is_all_steps_completed == true
sql postgresql activerecord ruby-on-rails-5
asked Nov 15 '18 at 18:37
Elijah HallElijah Hall
306
add a comment |
0
0
0
I have four related models:
class Quest < ApplicationRecord
has_many :steps, -> { order(step_number: :asc) }
def last_step_number
steps.maximum(:step_number)
end
end
class Step < ApplicationRecord
validates :step_number, presence: true
belongs_to :quest
end
class Teamquest < ApplicationRecord
validates :last_step_completed, presence: true
belongs_to :quest
def is_all_steps_completed
last_step_completed == quest.last_step_number
end
end
I would like to add a scope to Teamquest that returns all records where Teamquest model method is_all_steps_completed == true
sql postgresql activerecord ruby-on-rails-5
asked Nov 15 '18 at 18:37
Elijah HallElijah Hall
306
I have four related models:
class Quest < ApplicationRecord
has_many :steps, -> { order(step_number: :asc) }
def last_step_number
steps.maximum(:step_number)
end
end
class Step < ApplicationRecord
validates :step_number, presence: true
belongs_to :quest
end
class Teamquest < ApplicationRecord
validates :last_step_completed, presence: true
belongs_to :quest
def is_all_steps_completed
last_step_completed == quest.last_step_number
end
end
I would like to add a scope to Teamquest that returns all records where Teamquest model method is_all_steps_completed == true
sql postgresql activerecord ruby-on-rails-5
sql postgresql activerecord ruby-on-rails-5
asked Nov 15 '18 at 18:37
Elijah HallElijah Hall
306
asked Nov 15 '18 at 18:37
Elijah HallElijah Hall
306
edited Nov 15 '18 at 19:04
Elijah Hall
asked Nov 15 '18 at 18:37
Elijah HallElijah Hall
306
asked Nov 15 '18 at 18:37
Elijah HallElijah Hall
306
asked Nov 15 '18 at 18:37
Elijah HallElijah Hall
306
306
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
This will work, a join with a select which returns only the row with the maximum step:
Teamquest.joins("
INNER JOIN (
SELECT quest_id, MAX(step_number) AS max_step
FROM steps
GROUP BY quest_id) max_steps
ON max_steps.quest_id = teamquests.quest_id"
).where("last_step_completed = max_steps.max_step")
I would really consider denormalizing your database and storing steps_count in quests table to make everything easier.
answered Nov 15 '18 at 19:09
Marcin KołodziejMarcin Kołodziej
4,5001315
This solved my problem perfectly. I attempted to create a scope on the Step model like this: scope :last_steps, -> { select('quest_id, MAX(step_number) AS max_step').group(:quest_id) } but I can't use it due to a postgres error PG::GroupingError: ERROR: column "steps.id" must appear in the GROUP BY clause or be used in an aggregate function
– Elijah Hall
Nov 15 '18 at 20:33
How are you trying to use it? Using only the scope, as inStep.last_stepsshould work.
– Marcin Kołodziej
Nov 15 '18 at 20:37
I was trying to use Step.last_steps.count and Step.last_steps.first -- neither of which worked. I have since discovered that I can use Step.last_steps.each do ... which meets my needs perfectly. Thanks!
– Elijah Hall
Nov 15 '18 at 21:00
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
});
}
});
draft saved
draft discarded
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%2f53325926%2fhow-to-create-a-scope-for-records-with-highest-value-in-associated-table-column%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
1
This will work, a join with a select which returns only the row with the maximum step:
Teamquest.joins("
INNER JOIN (
SELECT quest_id, MAX(step_number) AS max_step
FROM steps
GROUP BY quest_id) max_steps
ON max_steps.quest_id = teamquests.quest_id"
).where("last_step_completed = max_steps.max_step")
I would really consider denormalizing your database and storing steps_count in quests table to make everything easier.
answered Nov 15 '18 at 19:09
Marcin KołodziejMarcin Kołodziej
4,5001315
This solved my problem perfectly. I attempted to create a scope on the Step model like this: scope :last_steps, -> { select('quest_id, MAX(step_number) AS max_step').group(:quest_id) } but I can't use it due to a postgres error PG::GroupingError: ERROR: column "steps.id" must appear in the GROUP BY clause or be used in an aggregate function
– Elijah Hall
Nov 15 '18 at 20:33
How are you trying to use it? Using only the scope, as inStep.last_stepsshould work.
– Marcin Kołodziej
Nov 15 '18 at 20:37
I was trying to use Step.last_steps.count and Step.last_steps.first -- neither of which worked. I have since discovered that I can use Step.last_steps.each do ... which meets my needs perfectly. Thanks!
– Elijah Hall
Nov 15 '18 at 21:00
add a comment |
1
This will work, a join with a select which returns only the row with the maximum step:
Teamquest.joins("
INNER JOIN (
SELECT quest_id, MAX(step_number) AS max_step
FROM steps
GROUP BY quest_id) max_steps
ON max_steps.quest_id = teamquests.quest_id"
).where("last_step_completed = max_steps.max_step")
I would really consider denormalizing your database and storing steps_count in quests table to make everything easier.
answered Nov 15 '18 at 19:09
Marcin KołodziejMarcin Kołodziej
4,5001315
This solved my problem perfectly. I attempted to create a scope on the Step model like this: scope :last_steps, -> { select('quest_id, MAX(step_number) AS max_step').group(:quest_id) } but I can't use it due to a postgres error PG::GroupingError: ERROR: column "steps.id" must appear in the GROUP BY clause or be used in an aggregate function
– Elijah Hall
Nov 15 '18 at 20:33
How are you trying to use it? Using only the scope, as inStep.last_stepsshould work.
– Marcin Kołodziej
Nov 15 '18 at 20:37
I was trying to use Step.last_steps.count and Step.last_steps.first -- neither of which worked. I have since discovered that I can use Step.last_steps.each do ... which meets my needs perfectly. Thanks!
– Elijah Hall
Nov 15 '18 at 21:00
add a comment |
1
1
1
This will work, a join with a select which returns only the row with the maximum step:
Teamquest.joins("
INNER JOIN (
SELECT quest_id, MAX(step_number) AS max_step
FROM steps
GROUP BY quest_id) max_steps
ON max_steps.quest_id = teamquests.quest_id"
).where("last_step_completed = max_steps.max_step")
I would really consider denormalizing your database and storing steps_count in quests table to make everything easier.
answered Nov 15 '18 at 19:09
Marcin KołodziejMarcin Kołodziej
4,5001315
This will work, a join with a select which returns only the row with the maximum step:
Teamquest.joins("
INNER JOIN (
SELECT quest_id, MAX(step_number) AS max_step
FROM steps
GROUP BY quest_id) max_steps
ON max_steps.quest_id = teamquests.quest_id"
).where("last_step_completed = max_steps.max_step")
I would really consider denormalizing your database and storing steps_count in quests table to make everything easier.
answered Nov 15 '18 at 19:09
Marcin KołodziejMarcin Kołodziej
4,5001315
answered Nov 15 '18 at 19:09
Marcin KołodziejMarcin Kołodziej
4,5001315
answered Nov 15 '18 at 19:09
Marcin KołodziejMarcin Kołodziej
4,5001315
answered Nov 15 '18 at 19:09
Marcin KołodziejMarcin Kołodziej
4,5001315
4,5001315
This solved my problem perfectly. I attempted to create a scope on the Step model like this: scope :last_steps, -> { select('quest_id, MAX(step_number) AS max_step').group(:quest_id) } but I can't use it due to a postgres error PG::GroupingError: ERROR: column "steps.id" must appear in the GROUP BY clause or be used in an aggregate function
– Elijah Hall
Nov 15 '18 at 20:33
How are you trying to use it? Using only the scope, as inStep.last_stepsshould work.
– Marcin Kołodziej
Nov 15 '18 at 20:37
I was trying to use Step.last_steps.count and Step.last_steps.first -- neither of which worked. I have since discovered that I can use Step.last_steps.each do ... which meets my needs perfectly. Thanks!
– Elijah Hall
Nov 15 '18 at 21:00
add a comment |
This solved my problem perfectly. I attempted to create a scope on the Step model like this: scope :last_steps, -> { select('quest_id, MAX(step_number) AS max_step').group(:quest_id) } but I can't use it due to a postgres error PG::GroupingError: ERROR: column "steps.id" must appear in the GROUP BY clause or be used in an aggregate function
– Elijah Hall
Nov 15 '18 at 20:33
How are you trying to use it? Using only the scope, as inStep.last_stepsshould work.
– Marcin Kołodziej
Nov 15 '18 at 20:37
I was trying to use Step.last_steps.count and Step.last_steps.first -- neither of which worked. I have since discovered that I can use Step.last_steps.each do ... which meets my needs perfectly. Thanks!
– Elijah Hall
Nov 15 '18 at 21:00
This solved my problem perfectly. I attempted to create a scope on the Step model like this: scope :last_steps, -> { select('quest_id, MAX(step_number) AS max_step').group(:quest_id) } but I can't use it due to a postgres error PG::GroupingError: ERROR: column "steps.id" must appear in the GROUP BY clause or be used in an aggregate function
– Elijah Hall
Nov 15 '18 at 20:33
This solved my problem perfectly. I attempted to create a scope on the Step model like this: scope :last_steps, -> { select('quest_id, MAX(step_number) AS max_step').group(:quest_id) } but I can't use it due to a postgres error PG::GroupingError: ERROR: column "steps.id" must appear in the GROUP BY clause or be used in an aggregate function
– Elijah Hall
Nov 15 '18 at 20:33
How are you trying to use it? Using only the scope, as in
Step.last_steps should work.– Marcin Kołodziej
Nov 15 '18 at 20:37
How are you trying to use it? Using only the scope, as in
Step.last_steps should work.– Marcin Kołodziej
Nov 15 '18 at 20:37
I was trying to use Step.last_steps.count and Step.last_steps.first -- neither of which worked. I have since discovered that I can use Step.last_steps.each do ... which meets my needs perfectly. Thanks!
– Elijah Hall
Nov 15 '18 at 21:00
I was trying to use Step.last_steps.count and Step.last_steps.first -- neither of which worked. I have since discovered that I can use Step.last_steps.each do ... which meets my needs perfectly. Thanks!
– Elijah Hall
Nov 15 '18 at 21:00
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53325926%2fhow-to-create-a-scope-for-records-with-highest-value-in-associated-table-column%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
