GroupBy Email & get earliest created_at date in Group - Laravel Eloquent
How can I get the earliest created_at date when using groupBy in an eloquent statement?
I have a table with the following:
| id | name | email | created_at |
|----|--------|---------|---------------------|
| 1 | test | 1@1.com | 2018-01-01 09:00:00 |
| 2 | test | 1@1.com | 2018-01-02 09:00:00 |
| 3 | test 2 | 2@2.com | 2018-01-02 09:00:00 |
| 4 | test 2 | 2@2.com | 2018-01-03 09:00:00 |
| 5 | test | 1@1.com | 2018-01-03 09:00:00 |
| 6 | test | 1@1.com | 2018-01-04 09:00:00 |
| 7 | test | 1@1.com | 2018-01-05 09:00:00 |
| 8 | test 3 | 3@3.com | 2018-01-06 09:00:00 |
| 9 | test 3 | 3@3.com | 2018-01-07 09:00:00 |
when running the following query:
Customers::OrderBy('created_at', 'desc')->groupBy('email')->paginate('5');
I was expecting to get the results in the following order:
Test 3 (created: 2018-01-07)
Test (created: 2018-01-05)
Test 2 (created: 2018-01-03)
But instead I am getting so it doesn't look like it's respecting the OrderBy
Test 3 (created: 2018-01-06)
Test 2 (created: 2018-01-02)
Test (created: 2018-01-01)
Here is the SQL fiddle
http://sqlfiddle.com/#!9/89ee39/2
# EDIT - Answer based on response
Thanks to Nesku, finally query to get this working was:
return DB::table('customers')
->select(DB::raw('email, max(created_at)'))
->groupBy('email')
->orderBy('max(created_at)', 'desc')
->get();
php mysql laravel eloquent
asked Nov 23 '18 at 17:40
just_chrisjust_chris
949
add a comment |
How can I get the earliest created_at date when using groupBy in an eloquent statement?
I have a table with the following:
| id | name | email | created_at |
|----|--------|---------|---------------------|
| 1 | test | 1@1.com | 2018-01-01 09:00:00 |
| 2 | test | 1@1.com | 2018-01-02 09:00:00 |
| 3 | test 2 | 2@2.com | 2018-01-02 09:00:00 |
| 4 | test 2 | 2@2.com | 2018-01-03 09:00:00 |
| 5 | test | 1@1.com | 2018-01-03 09:00:00 |
| 6 | test | 1@1.com | 2018-01-04 09:00:00 |
| 7 | test | 1@1.com | 2018-01-05 09:00:00 |
| 8 | test 3 | 3@3.com | 2018-01-06 09:00:00 |
| 9 | test 3 | 3@3.com | 2018-01-07 09:00:00 |
when running the following query:
Customers::OrderBy('created_at', 'desc')->groupBy('email')->paginate('5');
I was expecting to get the results in the following order:
Test 3 (created: 2018-01-07)
Test (created: 2018-01-05)
Test 2 (created: 2018-01-03)
But instead I am getting so it doesn't look like it's respecting the OrderBy
Test 3 (created: 2018-01-06)
Test 2 (created: 2018-01-02)
Test (created: 2018-01-01)
Here is the SQL fiddle
http://sqlfiddle.com/#!9/89ee39/2
# EDIT - Answer based on response
Thanks to Nesku, finally query to get this working was:
return DB::table('customers')
->select(DB::raw('email, max(created_at)'))
->groupBy('email')
->orderBy('max(created_at)', 'desc')
->get();
php mysql laravel eloquent
asked Nov 23 '18 at 17:40
just_chrisjust_chris
949
add a comment |
How can I get the earliest created_at date when using groupBy in an eloquent statement?
I have a table with the following:
| id | name | email | created_at |
|----|--------|---------|---------------------|
| 1 | test | 1@1.com | 2018-01-01 09:00:00 |
| 2 | test | 1@1.com | 2018-01-02 09:00:00 |
| 3 | test 2 | 2@2.com | 2018-01-02 09:00:00 |
| 4 | test 2 | 2@2.com | 2018-01-03 09:00:00 |
| 5 | test | 1@1.com | 2018-01-03 09:00:00 |
| 6 | test | 1@1.com | 2018-01-04 09:00:00 |
| 7 | test | 1@1.com | 2018-01-05 09:00:00 |
| 8 | test 3 | 3@3.com | 2018-01-06 09:00:00 |
| 9 | test 3 | 3@3.com | 2018-01-07 09:00:00 |
when running the following query:
Customers::OrderBy('created_at', 'desc')->groupBy('email')->paginate('5');
I was expecting to get the results in the following order:
Test 3 (created: 2018-01-07)
Test (created: 2018-01-05)
Test 2 (created: 2018-01-03)
But instead I am getting so it doesn't look like it's respecting the OrderBy
Test 3 (created: 2018-01-06)
Test 2 (created: 2018-01-02)
Test (created: 2018-01-01)
Here is the SQL fiddle
http://sqlfiddle.com/#!9/89ee39/2
# EDIT - Answer based on response
Thanks to Nesku, finally query to get this working was:
return DB::table('customers')
->select(DB::raw('email, max(created_at)'))
->groupBy('email')
->orderBy('max(created_at)', 'desc')
->get();
php mysql laravel eloquent
asked Nov 23 '18 at 17:40
just_chrisjust_chris
949
How can I get the earliest created_at date when using groupBy in an eloquent statement?
I have a table with the following:
| id | name | email | created_at |
|----|--------|---------|---------------------|
| 1 | test | 1@1.com | 2018-01-01 09:00:00 |
| 2 | test | 1@1.com | 2018-01-02 09:00:00 |
| 3 | test 2 | 2@2.com | 2018-01-02 09:00:00 |
| 4 | test 2 | 2@2.com | 2018-01-03 09:00:00 |
| 5 | test | 1@1.com | 2018-01-03 09:00:00 |
| 6 | test | 1@1.com | 2018-01-04 09:00:00 |
| 7 | test | 1@1.com | 2018-01-05 09:00:00 |
| 8 | test 3 | 3@3.com | 2018-01-06 09:00:00 |
| 9 | test 3 | 3@3.com | 2018-01-07 09:00:00 |
when running the following query:
Customers::OrderBy('created_at', 'desc')->groupBy('email')->paginate('5');
I was expecting to get the results in the following order:
Test 3 (created: 2018-01-07)
Test (created: 2018-01-05)
Test 2 (created: 2018-01-03)
But instead I am getting so it doesn't look like it's respecting the OrderBy
Test 3 (created: 2018-01-06)
Test 2 (created: 2018-01-02)
Test (created: 2018-01-01)
Here is the SQL fiddle
http://sqlfiddle.com/#!9/89ee39/2
# EDIT - Answer based on response
Thanks to Nesku, finally query to get this working was:
return DB::table('customers')
->select(DB::raw('email, max(created_at)'))
->groupBy('email')
->orderBy('max(created_at)', 'desc')
->get();
php mysql laravel eloquent
php mysql laravel eloquent
asked Nov 23 '18 at 17:40
just_chrisjust_chris
949
asked Nov 23 '18 at 17:40
just_chrisjust_chris
949
edited Nov 23 '18 at 20:12
just_chris
asked Nov 23 '18 at 17:40
just_chrisjust_chris
949
asked Nov 23 '18 at 17:40
just_chrisjust_chris
949
asked Nov 23 '18 at 17:40
just_chrisjust_chris
949
949
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can't order by created_at because it contains multiples values, you can however take the max of created_at for each email and order by that.
The SQL query would look like this :
SELECT email, max(created_at)
FROM `customers`
GROUP BY email
ORDER BY max(created_at) DESC
http://sqlfiddle.com/#!9/89ee39/12
answered Nov 23 '18 at 18:36
NeskuNesku
4231311
Thank you Nesku, worked perfectly :)
– just_chris
Nov 23 '18 at 20:10
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%2f53450987%2fgroupby-email-get-earliest-created-at-date-in-group-laravel-eloquent%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
You can't order by created_at because it contains multiples values, you can however take the max of created_at for each email and order by that.
The SQL query would look like this :
SELECT email, max(created_at)
FROM `customers`
GROUP BY email
ORDER BY max(created_at) DESC
http://sqlfiddle.com/#!9/89ee39/12
answered Nov 23 '18 at 18:36
NeskuNesku
4231311
Thank you Nesku, worked perfectly :)
– just_chris
Nov 23 '18 at 20:10
add a comment |
You can't order by created_at because it contains multiples values, you can however take the max of created_at for each email and order by that.
The SQL query would look like this :
SELECT email, max(created_at)
FROM `customers`
GROUP BY email
ORDER BY max(created_at) DESC
http://sqlfiddle.com/#!9/89ee39/12
answered Nov 23 '18 at 18:36
NeskuNesku
4231311
Thank you Nesku, worked perfectly :)
– just_chris
Nov 23 '18 at 20:10
add a comment |
You can't order by created_at because it contains multiples values, you can however take the max of created_at for each email and order by that.
The SQL query would look like this :
SELECT email, max(created_at)
FROM `customers`
GROUP BY email
ORDER BY max(created_at) DESC
http://sqlfiddle.com/#!9/89ee39/12
answered Nov 23 '18 at 18:36
NeskuNesku
4231311
You can't order by created_at because it contains multiples values, you can however take the max of created_at for each email and order by that.
The SQL query would look like this :
SELECT email, max(created_at)
FROM `customers`
GROUP BY email
ORDER BY max(created_at) DESC
http://sqlfiddle.com/#!9/89ee39/12
answered Nov 23 '18 at 18:36
NeskuNesku
4231311
answered Nov 23 '18 at 18:36
NeskuNesku
4231311
answered Nov 23 '18 at 18:36
NeskuNesku
4231311
answered Nov 23 '18 at 18:36
NeskuNesku
4231311
4231311
Thank you Nesku, worked perfectly :)
– just_chris
Nov 23 '18 at 20:10
add a comment |
Thank you Nesku, worked perfectly :)
– just_chris
Nov 23 '18 at 20:10
Thank you Nesku, worked perfectly :)
– just_chris
Nov 23 '18 at 20:10
Thank you Nesku, worked perfectly :)
– just_chris
Nov 23 '18 at 20:10
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%2f53450987%2fgroupby-email-get-earliest-created-at-date-in-group-laravel-eloquent%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
