Is it ok to use COUNT queries?
Is it OK to use the number of rows in result returned by query (COUNT() function in MySQL) for any checks and so on?
For example if I want to check how many posts have the user made today to check if he can create another (in another words, have the user reached his daily limit), is it a good practice to just send a query like this
SELECT COUNT(post_text) FROM posts WHERE (date_published = CURDATE() AND userId = 115);
or is there a better approach. I faced this a couple of times (I don't write database logic often) and it always kinda confused me if I get this wrong or not. So hope you will just clarify this for me once and for all, thanks.
mysql sql
asked Nov 21 '18 at 11:45
Jack Ashton
508
add a comment |
Is it OK to use the number of rows in result returned by query (COUNT() function in MySQL) for any checks and so on?
For example if I want to check how many posts have the user made today to check if he can create another (in another words, have the user reached his daily limit), is it a good practice to just send a query like this
SELECT COUNT(post_text) FROM posts WHERE (date_published = CURDATE() AND userId = 115);
or is there a better approach. I faced this a couple of times (I don't write database logic often) and it always kinda confused me if I get this wrong or not. So hope you will just clarify this for me once and for all, thanks.
mysql sql
asked Nov 21 '18 at 11:45
Jack Ashton
508
6
If you want to count the number of rows, thenCOUNT()is the right function. Why would you not use it?
– Gordon Linoff
Nov 21 '18 at 11:45
So I want to check if user has reached his limit and the implementation seems to be to justCOUNTthe rows, maybe there is nothing wrong, but I'm just not sure
– Jack Ashton
Nov 21 '18 at 11:48
You can useCOUNT()just fine on the example topic that you provided.
– Martin
Nov 21 '18 at 11:48
Your current query makes no reference to the user. You may need to amend your where condition appropriately.
– P.Salmon
Nov 21 '18 at 11:48
Yeah, the query has nothing to do with my project, but I will fix it anyway
– Jack Ashton
Nov 21 '18 at 11:49
add a comment |
Is it OK to use the number of rows in result returned by query (COUNT() function in MySQL) for any checks and so on?
For example if I want to check how many posts have the user made today to check if he can create another (in another words, have the user reached his daily limit), is it a good practice to just send a query like this
SELECT COUNT(post_text) FROM posts WHERE (date_published = CURDATE() AND userId = 115);
or is there a better approach. I faced this a couple of times (I don't write database logic often) and it always kinda confused me if I get this wrong or not. So hope you will just clarify this for me once and for all, thanks.
mysql sql
asked Nov 21 '18 at 11:45
Jack Ashton
508
Is it OK to use the number of rows in result returned by query (COUNT() function in MySQL) for any checks and so on?
For example if I want to check how many posts have the user made today to check if he can create another (in another words, have the user reached his daily limit), is it a good practice to just send a query like this
SELECT COUNT(post_text) FROM posts WHERE (date_published = CURDATE() AND userId = 115);
or is there a better approach. I faced this a couple of times (I don't write database logic often) and it always kinda confused me if I get this wrong or not. So hope you will just clarify this for me once and for all, thanks.
mysql sql
mysql sql
asked Nov 21 '18 at 11:45
Jack Ashton
508
asked Nov 21 '18 at 11:45
Jack Ashton
508
edited Nov 21 '18 at 11:51
asked Nov 21 '18 at 11:45
Jack Ashton
508
asked Nov 21 '18 at 11:45
Jack Ashton
508
asked Nov 21 '18 at 11:45
Jack Ashton
508
508
6
If you want to count the number of rows, thenCOUNT()is the right function. Why would you not use it?
– Gordon Linoff
Nov 21 '18 at 11:45
So I want to check if user has reached his limit and the implementation seems to be to justCOUNTthe rows, maybe there is nothing wrong, but I'm just not sure
– Jack Ashton
Nov 21 '18 at 11:48
You can useCOUNT()just fine on the example topic that you provided.
– Martin
Nov 21 '18 at 11:48
Your current query makes no reference to the user. You may need to amend your where condition appropriately.
– P.Salmon
Nov 21 '18 at 11:48
Yeah, the query has nothing to do with my project, but I will fix it anyway
– Jack Ashton
Nov 21 '18 at 11:49
add a comment |
6
If you want to count the number of rows, thenCOUNT()is the right function. Why would you not use it?
– Gordon Linoff
Nov 21 '18 at 11:45
So I want to check if user has reached his limit and the implementation seems to be to justCOUNTthe rows, maybe there is nothing wrong, but I'm just not sure
– Jack Ashton
Nov 21 '18 at 11:48
You can useCOUNT()just fine on the example topic that you provided.
– Martin
Nov 21 '18 at 11:48
Your current query makes no reference to the user. You may need to amend your where condition appropriately.
– P.Salmon
Nov 21 '18 at 11:48
Yeah, the query has nothing to do with my project, but I will fix it anyway
– Jack Ashton
Nov 21 '18 at 11:49
6
6
If you want to count the number of rows, then
COUNT() is the right function. Why would you not use it?– Gordon Linoff
Nov 21 '18 at 11:45
If you want to count the number of rows, then
COUNT() is the right function. Why would you not use it?– Gordon Linoff
Nov 21 '18 at 11:45
So I want to check if user has reached his limit and the implementation seems to be to just
COUNT the rows, maybe there is nothing wrong, but I'm just not sure– Jack Ashton
Nov 21 '18 at 11:48
So I want to check if user has reached his limit and the implementation seems to be to just
COUNT the rows, maybe there is nothing wrong, but I'm just not sure– Jack Ashton
Nov 21 '18 at 11:48
You can use
COUNT() just fine on the example topic that you provided.– Martin
Nov 21 '18 at 11:48
You can use
COUNT() just fine on the example topic that you provided.– Martin
Nov 21 '18 at 11:48
Your current query makes no reference to the user. You may need to amend your where condition appropriately.
– P.Salmon
Nov 21 '18 at 11:48
Your current query makes no reference to the user. You may need to amend your where condition appropriately.
– P.Salmon
Nov 21 '18 at 11:48
Yeah, the query has nothing to do with my project, but I will fix it anyway
– Jack Ashton
Nov 21 '18 at 11:49
Yeah, the query has nothing to do with my project, but I will fix it anyway
– Jack Ashton
Nov 21 '18 at 11:49
add a comment |
1 Answer
1
active
oldest
votes
If a user has daily limits, then you probably want to do this check in the database.
In that case, you would implement this restriction using a trigger rather than at the application level. This ensures that the restriction is always applied, regardless of competing threads, table locks, or who is doing the update.
If you do want to implement the restriction at the application level, then you would use a query, presumably with count(). I would expect the query to include the user id:
SELECT COUNT(*)
FROM posts p
WHERE p.date_published = CURDATE() AND p.user_id = ?;
answered Nov 21 '18 at 11:49
Gordon Linoff
758k35291399
Yeah, now I see, so that was actually my question (maybe the name is confusing, but still), I wanted to know what is the better way to specify this kind of limits, and triggers seems to be better, so thanks for clarifying.
– Jack Ashton
Nov 21 '18 at 12:06
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%2f53411338%2fis-it-ok-to-use-count-queries%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
If a user has daily limits, then you probably want to do this check in the database.
In that case, you would implement this restriction using a trigger rather than at the application level. This ensures that the restriction is always applied, regardless of competing threads, table locks, or who is doing the update.
If you do want to implement the restriction at the application level, then you would use a query, presumably with count(). I would expect the query to include the user id:
SELECT COUNT(*)
FROM posts p
WHERE p.date_published = CURDATE() AND p.user_id = ?;
answered Nov 21 '18 at 11:49
Gordon Linoff
758k35291399
Yeah, now I see, so that was actually my question (maybe the name is confusing, but still), I wanted to know what is the better way to specify this kind of limits, and triggers seems to be better, so thanks for clarifying.
– Jack Ashton
Nov 21 '18 at 12:06
add a comment |
If a user has daily limits, then you probably want to do this check in the database.
In that case, you would implement this restriction using a trigger rather than at the application level. This ensures that the restriction is always applied, regardless of competing threads, table locks, or who is doing the update.
If you do want to implement the restriction at the application level, then you would use a query, presumably with count(). I would expect the query to include the user id:
SELECT COUNT(*)
FROM posts p
WHERE p.date_published = CURDATE() AND p.user_id = ?;
answered Nov 21 '18 at 11:49
Gordon Linoff
758k35291399
Yeah, now I see, so that was actually my question (maybe the name is confusing, but still), I wanted to know what is the better way to specify this kind of limits, and triggers seems to be better, so thanks for clarifying.
– Jack Ashton
Nov 21 '18 at 12:06
add a comment |
If a user has daily limits, then you probably want to do this check in the database.
In that case, you would implement this restriction using a trigger rather than at the application level. This ensures that the restriction is always applied, regardless of competing threads, table locks, or who is doing the update.
If you do want to implement the restriction at the application level, then you would use a query, presumably with count(). I would expect the query to include the user id:
SELECT COUNT(*)
FROM posts p
WHERE p.date_published = CURDATE() AND p.user_id = ?;
answered Nov 21 '18 at 11:49
Gordon Linoff
758k35291399
If a user has daily limits, then you probably want to do this check in the database.
In that case, you would implement this restriction using a trigger rather than at the application level. This ensures that the restriction is always applied, regardless of competing threads, table locks, or who is doing the update.
If you do want to implement the restriction at the application level, then you would use a query, presumably with count(). I would expect the query to include the user id:
SELECT COUNT(*)
FROM posts p
WHERE p.date_published = CURDATE() AND p.user_id = ?;
answered Nov 21 '18 at 11:49
Gordon Linoff
758k35291399
answered Nov 21 '18 at 11:49
Gordon Linoff
758k35291399
answered Nov 21 '18 at 11:49
Gordon Linoff
758k35291399
answered Nov 21 '18 at 11:49
Gordon Linoff
758k35291399
758k35291399
Yeah, now I see, so that was actually my question (maybe the name is confusing, but still), I wanted to know what is the better way to specify this kind of limits, and triggers seems to be better, so thanks for clarifying.
– Jack Ashton
Nov 21 '18 at 12:06
add a comment |
Yeah, now I see, so that was actually my question (maybe the name is confusing, but still), I wanted to know what is the better way to specify this kind of limits, and triggers seems to be better, so thanks for clarifying.
– Jack Ashton
Nov 21 '18 at 12:06
Yeah, now I see, so that was actually my question (maybe the name is confusing, but still), I wanted to know what is the better way to specify this kind of limits, and triggers seems to be better, so thanks for clarifying.
– Jack Ashton
Nov 21 '18 at 12:06
Yeah, now I see, so that was actually my question (maybe the name is confusing, but still), I wanted to know what is the better way to specify this kind of limits, and triggers seems to be better, so thanks for clarifying.
– Jack Ashton
Nov 21 '18 at 12:06
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53411338%2fis-it-ok-to-use-count-queries%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
6
If you want to count the number of rows, then
COUNT()is the right function. Why would you not use it?– Gordon Linoff
Nov 21 '18 at 11:45
So I want to check if user has reached his limit and the implementation seems to be to just
COUNTthe rows, maybe there is nothing wrong, but I'm just not sure– Jack Ashton
Nov 21 '18 at 11:48
You can use
COUNT()just fine on the example topic that you provided.– Martin
Nov 21 '18 at 11:48
Your current query makes no reference to the user. You may need to amend your where condition appropriately.
– P.Salmon
Nov 21 '18 at 11:48
Yeah, the query has nothing to do with my project, but I will fix it anyway
– Jack Ashton
Nov 21 '18 at 11:49