CODEWITHSUNDEEP
phplaravelsqlitelaravel-5laravel-5.4
Indra Sukmajaya
Indra Sukmajaya

Reputation: 95

Laravel 5.4 - Get one data from each duplicate data row

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"
    },
]

Upvotes: 0

Views: 1800

Answers (4)

Sohel0415
Sohel0415

Reputation: 9853

Use groupBy():

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

Upvotes: 0

Anton De Rose
Anton De Rose

Reputation: 102

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;

Upvotes: 2

Mahdi Younesi
Mahdi Younesi

Reputation: 7489

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

Upvotes: 1

Sagar Gautam
Sagar Gautam

Reputation: 9369

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.

Upvotes: 1

Related Questions