Remove duplicates after filtering by foreignkey
0
I am currently writing a flask webapp. I have 2 tables, 1 for users and 1 for book titles. I have a foreignkey in book titles to relate it to the user who entered it. I want to remove duplicates but only on a per user basis (aka, each user can have the same title associated with them, but I don't want a duplicate title for one user)
How can I remove duplicates (or better yet, update old entry with new date) based on first sorting by the user.id (foreignkey)?
python sql flask sqlalchemy
asked Dec 29 '18 at 18:51
CUbuffsFTWCUbuffsFTW
246
|
show 5 more comments
0
I am currently writing a flask webapp. I have 2 tables, 1 for users and 1 for book titles. I have a foreignkey in book titles to relate it to the user who entered it. I want to remove duplicates but only on a per user basis (aka, each user can have the same title associated with them, but I don't want a duplicate title for one user)
How can I remove duplicates (or better yet, update old entry with new date) based on first sorting by the user.id (foreignkey)?
python sql flask sqlalchemy
asked Dec 29 '18 at 18:51
CUbuffsFTWCUbuffsFTW
246
Can you provide the code you used to attempt the solution ?
– AK47
Dec 29 '18 at 18:52
A basic table schema of the two tables would be extremely helpful along with any code you're already attempted. I'm curious why you are allowing duplicate books in the book table, but this is something that could generally be solved by a GROUP BY book id and then using an appropriate aggregate clause for the user id (sort by most recent addition, etc.).
– Jason Baumgartner
Dec 29 '18 at 18:56
@AK47 No attempt yet unfortunately as this is where I'm stuck :(
– CUbuffsFTW
Dec 29 '18 at 19:05
2
Right. You need a table just for books and a table that associates users with books. So if a book does't exist and I am a new user, I might get a user id of 123 and I may enter a book 456. You need to check if the book exists in the book table and if it doesn't, put the book in that able (only one row per unique book) and then go to the user_book table and put in my user_id and book_id along with the date of when I added the book. That way you avoid duplicates in your book table. Right now your DB sounds a bit denormalized.
– Jason Baumgartner
Dec 29 '18 at 19:20
1
@JasonBaumgartner Oh, thank you! I think that makes sense now!
– CUbuffsFTW
Dec 29 '18 at 19:25
|
show 5 more comments
0
0
0
I am currently writing a flask webapp. I have 2 tables, 1 for users and 1 for book titles. I have a foreignkey in book titles to relate it to the user who entered it. I want to remove duplicates but only on a per user basis (aka, each user can have the same title associated with them, but I don't want a duplicate title for one user)
How can I remove duplicates (or better yet, update old entry with new date) based on first sorting by the user.id (foreignkey)?
python sql flask sqlalchemy
asked Dec 29 '18 at 18:51
CUbuffsFTWCUbuffsFTW
246
I am currently writing a flask webapp. I have 2 tables, 1 for users and 1 for book titles. I have a foreignkey in book titles to relate it to the user who entered it. I want to remove duplicates but only on a per user basis (aka, each user can have the same title associated with them, but I don't want a duplicate title for one user)
How can I remove duplicates (or better yet, update old entry with new date) based on first sorting by the user.id (foreignkey)?
python sql flask sqlalchemy
python sql flask sqlalchemy
asked Dec 29 '18 at 18:51
CUbuffsFTWCUbuffsFTW
246
asked Dec 29 '18 at 18:51
CUbuffsFTWCUbuffsFTW
246
asked Dec 29 '18 at 18:51
CUbuffsFTWCUbuffsFTW
246
asked Dec 29 '18 at 18:51
CUbuffsFTWCUbuffsFTW
246
asked Dec 29 '18 at 18:51
CUbuffsFTWCUbuffsFTW
246
246
Can you provide the code you used to attempt the solution ?
– AK47
Dec 29 '18 at 18:52
A basic table schema of the two tables would be extremely helpful along with any code you're already attempted. I'm curious why you are allowing duplicate books in the book table, but this is something that could generally be solved by a GROUP BY book id and then using an appropriate aggregate clause for the user id (sort by most recent addition, etc.).
– Jason Baumgartner
Dec 29 '18 at 18:56
@AK47 No attempt yet unfortunately as this is where I'm stuck :(
– CUbuffsFTW
Dec 29 '18 at 19:05
2
Right. You need a table just for books and a table that associates users with books. So if a book does't exist and I am a new user, I might get a user id of 123 and I may enter a book 456. You need to check if the book exists in the book table and if it doesn't, put the book in that able (only one row per unique book) and then go to the user_book table and put in my user_id and book_id along with the date of when I added the book. That way you avoid duplicates in your book table. Right now your DB sounds a bit denormalized.
– Jason Baumgartner
Dec 29 '18 at 19:20
1
@JasonBaumgartner Oh, thank you! I think that makes sense now!
– CUbuffsFTW
Dec 29 '18 at 19:25
|
show 5 more comments
Can you provide the code you used to attempt the solution ?
– AK47
Dec 29 '18 at 18:52
A basic table schema of the two tables would be extremely helpful along with any code you're already attempted. I'm curious why you are allowing duplicate books in the book table, but this is something that could generally be solved by a GROUP BY book id and then using an appropriate aggregate clause for the user id (sort by most recent addition, etc.).
– Jason Baumgartner
Dec 29 '18 at 18:56
@AK47 No attempt yet unfortunately as this is where I'm stuck :(
– CUbuffsFTW
Dec 29 '18 at 19:05
2
Right. You need a table just for books and a table that associates users with books. So if a book does't exist and I am a new user, I might get a user id of 123 and I may enter a book 456. You need to check if the book exists in the book table and if it doesn't, put the book in that able (only one row per unique book) and then go to the user_book table and put in my user_id and book_id along with the date of when I added the book. That way you avoid duplicates in your book table. Right now your DB sounds a bit denormalized.
– Jason Baumgartner
Dec 29 '18 at 19:20
1
@JasonBaumgartner Oh, thank you! I think that makes sense now!
– CUbuffsFTW
Dec 29 '18 at 19:25
Can you provide the code you used to attempt the solution ?
– AK47
Dec 29 '18 at 18:52
Can you provide the code you used to attempt the solution ?
– AK47
Dec 29 '18 at 18:52
A basic table schema of the two tables would be extremely helpful along with any code you're already attempted. I'm curious why you are allowing duplicate books in the book table, but this is something that could generally be solved by a GROUP BY book id and then using an appropriate aggregate clause for the user id (sort by most recent addition, etc.).
– Jason Baumgartner
Dec 29 '18 at 18:56
A basic table schema of the two tables would be extremely helpful along with any code you're already attempted. I'm curious why you are allowing duplicate books in the book table, but this is something that could generally be solved by a GROUP BY book id and then using an appropriate aggregate clause for the user id (sort by most recent addition, etc.).
– Jason Baumgartner
Dec 29 '18 at 18:56
@AK47 No attempt yet unfortunately as this is where I'm stuck :(
– CUbuffsFTW
Dec 29 '18 at 19:05
@AK47 No attempt yet unfortunately as this is where I'm stuck :(
– CUbuffsFTW
Dec 29 '18 at 19:05
2
2
Right. You need a table just for books and a table that associates users with books. So if a book does't exist and I am a new user, I might get a user id of 123 and I may enter a book 456. You need to check if the book exists in the book table and if it doesn't, put the book in that able (only one row per unique book) and then go to the user_book table and put in my user_id and book_id along with the date of when I added the book. That way you avoid duplicates in your book table. Right now your DB sounds a bit denormalized.
– Jason Baumgartner
Dec 29 '18 at 19:20
Right. You need a table just for books and a table that associates users with books. So if a book does't exist and I am a new user, I might get a user id of 123 and I may enter a book 456. You need to check if the book exists in the book table and if it doesn't, put the book in that able (only one row per unique book) and then go to the user_book table and put in my user_id and book_id along with the date of when I added the book. That way you avoid duplicates in your book table. Right now your DB sounds a bit denormalized.
– Jason Baumgartner
Dec 29 '18 at 19:20
1
1
@JasonBaumgartner Oh, thank you! I think that makes sense now!
– CUbuffsFTW
Dec 29 '18 at 19:25
@JasonBaumgartner Oh, thank you! I think that makes sense now!
– CUbuffsFTW
Dec 29 '18 at 19:25
|
show 5 more comments
0
active
oldest
votes
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%2f53972443%2fremove-duplicates-after-filtering-by-foreignkey%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53972443%2fremove-duplicates-after-filtering-by-foreignkey%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
Can you provide the code you used to attempt the solution ?
– AK47
Dec 29 '18 at 18:52
A basic table schema of the two tables would be extremely helpful along with any code you're already attempted. I'm curious why you are allowing duplicate books in the book table, but this is something that could generally be solved by a GROUP BY book id and then using an appropriate aggregate clause for the user id (sort by most recent addition, etc.).
– Jason Baumgartner
Dec 29 '18 at 18:56
@AK47 No attempt yet unfortunately as this is where I'm stuck :(
– CUbuffsFTW
Dec 29 '18 at 19:05
2
Right. You need a table just for books and a table that associates users with books. So if a book does't exist and I am a new user, I might get a user id of 123 and I may enter a book 456. You need to check if the book exists in the book table and if it doesn't, put the book in that able (only one row per unique book) and then go to the user_book table and put in my user_id and book_id along with the date of when I added the book. That way you avoid duplicates in your book table. Right now your DB sounds a bit denormalized.
– Jason Baumgartner
Dec 29 '18 at 19:20
1
@JasonBaumgartner Oh, thank you! I think that makes sense now!
– CUbuffsFTW
Dec 29 '18 at 19:25