pmirnd pmirnd - 1 year ago 172
SQL Question

Eloquent and Laravel, get the MONTH and YEAR of a datetime

I have an Eloquent query that is giving me an error with the ´ ´ that Laravel put on it. If I copy-paste the same query as SQL in my database it works perfect, but I don't know hot the proceed with MONTH() and YEAR() correctly in Eloquent. I have this:

$datos = MyModel::select([
'id',
'period',
'MONTH(time_table.period) AS month',
'YEAR(time_table.period) AS year'])
->get();


and
period
is a datetime value. I need to retrieve the month and year of that 'period' value.

What can I do?

EDIT:

The error in the app is (with the original values):

(2/2) QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'MONTH(admin_fijacion_anteriores.periodo)' in 'field list' (SQL: select
id_fijacion
as
id
,
admin_distribuidoras
.
nombre
as
nombre_distribuidora
,
admin_licitaciones
.
nombre
as
nombre_licitacion
,
admin_generadoras
.
nombre
as
nombre_generadora
,
admin_bloques
.
nombre
as
nombre_bloques
,
periodo
,
energia
,
potencia
,
MONTH(admin_fijacion_anteriores
.
periodo)
as
mes
,
YEAR(admin_fijacion_anteriores
.
periodo)
as
anio
from
admin_fijacion_anteriores
inner join
admin_distribuidoras
on
admin_fijacion_anteriores
.
distribuidora_id
=
admin_distribuidoras
.
id_distribuidora
inner join
admin_licitaciones
on
admin_fijacion_anteriores
.
licitacion_id
=
admin_licitaciones
.
id_licitacion
inner join
admin_generadoras
on
admin_fijacion_anteriores
.
generadora_id
=
admin_generadoras
.
id_generadora
inner join
admin_bloques
on
admin_fijacion_anteriores
.
bloque_id
=
admin_bloques
.
id_bloque
)

and the code of my real Eloquent query is:

$datos = AdminFijacionAnteriore::select([
'id_fijacion AS id',
'admin_distribuidoras.nombre as nombre_distribuidora',
'admin_licitaciones.nombre as nombre_licitacion',
'admin_generadoras.nombre as nombre_generadora',
'admin_bloques.nombre as nombre_bloques',
'periodo',
'energia',
'potencia',
'MONTH(admin_fijacion_anteriores.periodo) AS mes',
'YEAR(admin_fijacion_anteriores.periodo) AS anio'])
->join('admin_distribuidoras', 'admin_fijacion_anteriores.distribuidora_id', '=', 'admin_distribuidoras.id_distribuidora')
->join('admin_licitaciones', 'admin_fijacion_anteriores.licitacion_id', '=', 'admin_licitaciones.id_licitacion')
->join('admin_generadoras', 'admin_fijacion_anteriores.generadora_id', '=', 'admin_generadoras.id_generadora')
->join('admin_bloques', 'admin_fijacion_anteriores.bloque_id', '=', 'admin_bloques.id_bloque')
->get();

Answer Source

Use selectRaw() instead of select().

In your case :

$datos = MyModel::selectRaw('
    id,
    period,
    MONTH(time_table.period) AS month,
    YEAR(time_table.period) AS year
')->get();

But in my opinion, the correct solution to your problem would be to use date mutators on your model, and then simply call $datos->period->month

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download