CODEWITHSUNDEEP
sqllaraveleloquent
pmiranda
pmiranda

Reputation: 8470

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();

Upvotes: 0

Views: 6333

Answers (1)

Cédric
Cédric

Reputation: 567

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

Upvotes: 3

Related Questions