Laravel 5.4 - Get one data from each duplicate data row

1.7k views Asked by At

I want to make select options to display the month taken from the created_at column of a table

// item_ins
+----+--------+---------------------+
| id |  item  |     created_at      |
+----+--------+---------------------+
|  1 | item 1 | 2017-12-11 10:37:52 |
|  2 | item 2 | 2017-12-11 10:38:17 |
|  3 | item 3 | 2018-01-12 01:28:43 |
|  4 | item 4 | 2018-01-12 01:30:14 |
|  5 | item 5 | 2018-02-12 01:30:05 |
|  6 | item 6 | 2018-02-12 01:30:42 |
+----+--------+---------------------+

in the table there are each two months december, january and february, and i want to get one from every same month, i try like this

$months= ItemIn::distinct('created_at')->pluck('created_at');

but I still get the same two months for each month because the timing is not the same, as below

and I'm still confused with how to get just a month because in the above way I get also years and days

[
    {
        "date": "2017-12-11 10:37:52.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2017-12-11 10:38:17.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2018-01-12 01:28:43.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2018-01-12 01:29:14.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2018-02-12 01:30:05.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
    {
        "date": "2018-02-12 01:30:22.000000",
        "timezone_type": 3,
        "timezone": "UTC"
    },
]
4

There are 4 answers

0
Sagar Gautam On

Use CAST with raw query to get your desire result like this,

$data = \DB::table('item_ins')
        ->select(\DB::raw('CAST(created_at as date) as created_at'))
        ->distinct('created_at')->get();

You can see docs here https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

I hope you will understand.

0
Anton De Rose On

if the presence of time is not an issue, you can handle this at the time of using the select query itself, if you are selecting, by using substring.

select id, item, SUBSTRING(created_at, 1, 11) as created_at
from test
group by created_at;
0
Mahdi Younesi On

Either format it in the view using Carbon

{{ \Carbon\Carbon::parse($user->from_date)->format('d/m/Y')}}

or use accessors in the model to reformat the date field

public function getCreatedAtAttribute($value) {
     return \Carbon\Carbon::parse($value)->format('m');
}

Then $model->created_at will be in desired format

0
Sohel0415 On

Use groupBy():

$months = ItemIn::groupBy(DB::raw('MONTH(created_at)'))->selectRaw('DATE_FORMAT(created_at, "%M") as month')->get();