Leave last/newest records in table by a combination of fields
How can I leave one record (last/newest) for each resource_owner_id and scopes combination and remove other records?
For example, for resource_owner_id=3 and scopes=driver it should be only record with id=1357.resource_owner_id=5 and scopes=driver - with id=1034
I know that I can use .ids, after that get all records by id and scope (scopes are enum - 'driver' and 'passenger'), after that use .pop and remove all remaining records.
Maybe there is a more elegant solution?
ruby-on-rails activerecord
asked Nov 27 '18 at 14:16
ViT-Vetal-ViT-Vetal-
1,13911025
add a comment |
How can I leave one record (last/newest) for each resource_owner_id and scopes combination and remove other records?
For example, for resource_owner_id=3 and scopes=driver it should be only record with id=1357.resource_owner_id=5 and scopes=driver - with id=1034
I know that I can use .ids, after that get all records by id and scope (scopes are enum - 'driver' and 'passenger'), after that use .pop and remove all remaining records.
Maybe there is a more elegant solution?
ruby-on-rails activerecord
asked Nov 27 '18 at 14:16
ViT-Vetal-ViT-Vetal-
1,13911025
What DB are you using?
– Marcin Kołodziej
Nov 27 '18 at 16:12
@MarcinKołodziej PostgreSQL
– ViT-Vetal-
Nov 27 '18 at 16:40
add a comment |
How can I leave one record (last/newest) for each resource_owner_id and scopes combination and remove other records?
For example, for resource_owner_id=3 and scopes=driver it should be only record with id=1357.resource_owner_id=5 and scopes=driver - with id=1034
I know that I can use .ids, after that get all records by id and scope (scopes are enum - 'driver' and 'passenger'), after that use .pop and remove all remaining records.
Maybe there is a more elegant solution?
ruby-on-rails activerecord
asked Nov 27 '18 at 14:16
ViT-Vetal-ViT-Vetal-
1,13911025
How can I leave one record (last/newest) for each resource_owner_id and scopes combination and remove other records?
For example, for resource_owner_id=3 and scopes=driver it should be only record with id=1357.resource_owner_id=5 and scopes=driver - with id=1034
I know that I can use .ids, after that get all records by id and scope (scopes are enum - 'driver' and 'passenger'), after that use .pop and remove all remaining records.
Maybe there is a more elegant solution?
ruby-on-rails activerecord
ruby-on-rails activerecord
asked Nov 27 '18 at 14:16
ViT-Vetal-ViT-Vetal-
1,13911025
asked Nov 27 '18 at 14:16
ViT-Vetal-ViT-Vetal-
1,13911025
asked Nov 27 '18 at 14:16
ViT-Vetal-ViT-Vetal-
1,13911025
asked Nov 27 '18 at 14:16
ViT-Vetal-ViT-Vetal-
1,13911025
asked Nov 27 '18 at 14:16
ViT-Vetal-ViT-Vetal-
1,13911025
1,13911025
What DB are you using?
– Marcin Kołodziej
Nov 27 '18 at 16:12
@MarcinKołodziej PostgreSQL
– ViT-Vetal-
Nov 27 '18 at 16:40
add a comment |
What DB are you using?
– Marcin Kołodziej
Nov 27 '18 at 16:12
@MarcinKołodziej PostgreSQL
– ViT-Vetal-
Nov 27 '18 at 16:40
What DB are you using?
– Marcin Kołodziej
Nov 27 '18 at 16:12
What DB are you using?
– Marcin Kołodziej
Nov 27 '18 at 16:12
@MarcinKołodziej PostgreSQL
– ViT-Vetal-
Nov 27 '18 at 16:40
@MarcinKołodziej PostgreSQL
– ViT-Vetal-
Nov 27 '18 at 16:40
add a comment |
1 Answer
1
active
oldest
votes
SQL Fiddle
ActiveRecord version:
Model.select("DISTINCT ON(resource_owner_id, scopes) *")
.order("resource_owner_id, scopes, id")
Read more about DISTINCT ON here.
answered Nov 27 '18 at 16:55
Marcin KołodziejMarcin Kołodziej
4,4901315
Thanks! But I need leave in table this records and remove all other records. Should I just add this lineModel.where.not(id:@models.map(&:id)).destroy_all?
– ViT-Vetal-
Nov 28 '18 at 10:06
Ah, I thought that by remove you mean remove them from the returned records. Yes, that would work, depending on what callbacks you have, you might considerdelete_allas it would be much faster.
– Marcin Kołodziej
Nov 28 '18 at 10:18
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%2f53501699%2fleave-last-newest-records-in-table-by-a-combination-of-fields%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
SQL Fiddle
ActiveRecord version:
Model.select("DISTINCT ON(resource_owner_id, scopes) *")
.order("resource_owner_id, scopes, id")
Read more about DISTINCT ON here.
answered Nov 27 '18 at 16:55
Marcin KołodziejMarcin Kołodziej
4,4901315
Thanks! But I need leave in table this records and remove all other records. Should I just add this lineModel.where.not(id:@models.map(&:id)).destroy_all?
– ViT-Vetal-
Nov 28 '18 at 10:06
Ah, I thought that by remove you mean remove them from the returned records. Yes, that would work, depending on what callbacks you have, you might considerdelete_allas it would be much faster.
– Marcin Kołodziej
Nov 28 '18 at 10:18
add a comment |
SQL Fiddle
ActiveRecord version:
Model.select("DISTINCT ON(resource_owner_id, scopes) *")
.order("resource_owner_id, scopes, id")
Read more about DISTINCT ON here.
answered Nov 27 '18 at 16:55
Marcin KołodziejMarcin Kołodziej
4,4901315
Thanks! But I need leave in table this records and remove all other records. Should I just add this lineModel.where.not(id:@models.map(&:id)).destroy_all?
– ViT-Vetal-
Nov 28 '18 at 10:06
Ah, I thought that by remove you mean remove them from the returned records. Yes, that would work, depending on what callbacks you have, you might considerdelete_allas it would be much faster.
– Marcin Kołodziej
Nov 28 '18 at 10:18
add a comment |
SQL Fiddle
ActiveRecord version:
Model.select("DISTINCT ON(resource_owner_id, scopes) *")
.order("resource_owner_id, scopes, id")
Read more about DISTINCT ON here.
answered Nov 27 '18 at 16:55
Marcin KołodziejMarcin Kołodziej
4,4901315
SQL Fiddle
ActiveRecord version:
Model.select("DISTINCT ON(resource_owner_id, scopes) *")
.order("resource_owner_id, scopes, id")
Read more about DISTINCT ON here.
answered Nov 27 '18 at 16:55
Marcin KołodziejMarcin Kołodziej
4,4901315
answered Nov 27 '18 at 16:55
Marcin KołodziejMarcin Kołodziej
4,4901315
answered Nov 27 '18 at 16:55
Marcin KołodziejMarcin Kołodziej
4,4901315
answered Nov 27 '18 at 16:55
Marcin KołodziejMarcin Kołodziej
4,4901315
4,4901315
Thanks! But I need leave in table this records and remove all other records. Should I just add this lineModel.where.not(id:@models.map(&:id)).destroy_all?
– ViT-Vetal-
Nov 28 '18 at 10:06
Ah, I thought that by remove you mean remove them from the returned records. Yes, that would work, depending on what callbacks you have, you might considerdelete_allas it would be much faster.
– Marcin Kołodziej
Nov 28 '18 at 10:18
add a comment |
Thanks! But I need leave in table this records and remove all other records. Should I just add this lineModel.where.not(id:@models.map(&:id)).destroy_all?
– ViT-Vetal-
Nov 28 '18 at 10:06
Ah, I thought that by remove you mean remove them from the returned records. Yes, that would work, depending on what callbacks you have, you might considerdelete_allas it would be much faster.
– Marcin Kołodziej
Nov 28 '18 at 10:18
Thanks! But I need leave in table this records and remove all other records. Should I just add this line
Model.where.not(id:@models.map(&:id)).destroy_all?– ViT-Vetal-
Nov 28 '18 at 10:06
Thanks! But I need leave in table this records and remove all other records. Should I just add this line
Model.where.not(id:@models.map(&:id)).destroy_all?– ViT-Vetal-
Nov 28 '18 at 10:06
Ah, I thought that by remove you mean remove them from the returned records. Yes, that would work, depending on what callbacks you have, you might consider
delete_all as it would be much faster.– Marcin Kołodziej
Nov 28 '18 at 10:18
Ah, I thought that by remove you mean remove them from the returned records. Yes, that would work, depending on what callbacks you have, you might consider
delete_all as it would be much faster.– Marcin Kołodziej
Nov 28 '18 at 10:18
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%2f53501699%2fleave-last-newest-records-in-table-by-a-combination-of-fields%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
What DB are you using?
– Marcin Kołodziej
Nov 27 '18 at 16:12
@MarcinKołodziej PostgreSQL
– ViT-Vetal-
Nov 27 '18 at 16:40