How to Get Newest Data With 'Created_At' Column

How to Get Newest Data with `created_at` Column

To get latest row per staflow attribute you can use a self join

select a.*
from demo a
join (
    select id_konsul,staflow, max(created_at) created_at
    from demo
    where id_konsul = 4
    group by id_konsul, staflow
) b on a.staflow = b.staflow
    and a.id_konsul = b.id_konsul 
    and a.created_at = b.created_at;

Or using a left join

select a.*
from demo a
left join demo b on a.id_konsul = b.id_konsul
                and a.staflow = b.staflow 
                and a.created_at < b.created_at
where a.id_konsul = 4 
and b.id_konsul is null

Demo

To write above queries using laravel's query builder you can use following references

Laravel Eloquent select all rows with max created_at

Laravel - Get the last entry of each UID type

Laravel Eloquent group by most recent record

How to pick latest Date in Created_at column in MySQL for Laravel

Now I would like to select borrowers who have not carried out any transaction for the past 30 days

You have to select Borrowers and not SavingTransaction

// suppose "savingTransactions" is hasMany relation defined in Borrowers model
$result = Borrowers::whereDoesntHave('savingTransactions', function($queryBuilder){
  $queryBuilder->whereDate('created_at','<=', Carbon::now()->subDays(30));
})->get();

If you not have the relation, you can also use ->whereNotExists(...)

How to Get Newest Data with `created_at` Column

To get latest row per staflow attribute you can use a self join

select a.*
from demo a
join (
    select id_konsul,staflow, max(created_at) created_at
    from demo
    where id_konsul = 4
    group by id_konsul, staflow
) b on a.staflow = b.staflow
    and a.id_konsul = b.id_konsul 
    and a.created_at = b.created_at;

Or using a left join

select a.*
from demo a
left join demo b on a.id_konsul = b.id_konsul
                and a.staflow = b.staflow 
                and a.created_at < b.created_at
where a.id_konsul = 4 
and b.id_konsul is null

Demo

To write above queries using laravel's query builder you can use following references

Laravel Eloquent select all rows with max created_at

Laravel - Get the last entry of each UID type

Laravel Eloquent group by most recent record

MySQL Query to select row into column with latest created_at date

You can use a correlated subquery to filter the table and retain the record that has the latest created_at for each panel_name and day of created_at. Then, you can use conditional aggregation to pivot the results:

select
    max(case when panel_name = 'TARA MUMBAI DAY' then panel_number end ) `TARA MUMBAI DAY`,
    max(case when panel_name = 'TIME' then panel_number end ) `TIME`,
    max(case when panel_name = 'MILAN DAY' then panel_number end ) `MILAN DAY`,
    max(case when panel_name = 'TARA MUMBAI NIGHT' then panel_number end ) `TARA MUMBAI NIGHT`,
    date(created_at) created_at
from mytable t
where created_at = (
    select max(t1.created_at)
    from mytable t1
    where 
        date(t1.created_at) = date(t.created_at)
        and t1.panel_name = t.panel_name
)
group by date(created_at)

Demo on DB Fiddlde

TARA MUMBAI DAY | TIME       | MILAN DAY  | TARA MUMBAI NIGHT | created_at
:-------------- | :--------- | :--------- | :---------------- | :---------
128-74-446      | ***-**-*** | 700-74-789 | 700-74-789        | 2019-11-11
123-1*-***      | 123-**-*** | null       | null              | 2019-11-13

How to retrieve data from the latest date in laravel?

use orderbBy():

TblModel::orderBy('date','DESC')->first();

Or

DB::table('tbl')->orderBy('date', 'DESC')->first();

Update:

TblModel::where('date', TblModel::max('date'))->orderBy('date','desc')->get();

How to add 'created_at' and 'updated_at' columns?

According to the reference manual* you can use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP,
the column has the current timestamp for its default value and is
automatically updated to the current timestamp.

whereas:

With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the
column has the given default value and is not automatically updated to
the current timestamp.

So, for example, you could use:

CREATE TABLE t1 (
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

To add the columns to an already existing table you can use:

ALTER TABLE t1
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Note: Link provided refers to MySQL 8.0. The syntax is the same for previous versions as well. There is some difference though for versions prior to 5.6.5. Just quoting from the manual again:

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically
initializated and updated to the current date and time (that is, the
current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and
for at most one TIMESTAMP column per table.

How to add 'created_at' and 'updated_at' columns?

According to the reference manual* you can use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP,
the column has the current timestamp for its default value and is
automatically updated to the current timestamp.

whereas:

With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the
column has the given default value and is not automatically updated to
the current timestamp.

So, for example, you could use:

CREATE TABLE t1 (
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

To add the columns to an already existing table you can use:

ALTER TABLE t1
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Note: Link provided refers to MySQL 8.0. The syntax is the same for previous versions as well. There is some difference though for versions prior to 5.6.5. Just quoting from the manual again:

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically
initializated and updated to the current date and time (that is, the
current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and
for at most one TIMESTAMP column per table.

How to select year and month from the created_at attributes of database table in laravel 5.1?

There are date helpers available in the query builder:

$post = Mjblog::whereYear('created_at', '=', $year)
              ->whereMonth('created_at', '=', $month)
              ->get();

---

Related Topics