problem when making a query in laravel

Question

problem when making a query in laravel

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

I make this query:

public static function searchYear($year)
{
    return DB:: table('contract_works')
    ->select(DB::raw('Year(dateFailure) as dt'))
    ->where('dt',$year)
    ->orderBy('dt','asc')
    ->get();
}

but it tells me that the field does not exist, getting this error:

SQLSTATE [42S22]: Column not found: 1054 Unknown column 'dt' in 'where clause' (SQL: select Year (dateFailure) as dt from% co_of% where% co_of% = 1970 order by% co_of% asc)

sql laravel

asked by Daniel Cruz Pablo 04.05.2016 в 17:17

source

2 answers

0

Use a subquery:

SELECT dt FROM 
(
SELECT Year(dateFailure) AS dt FROM contact_works
) AS table1 
WHERE dt = 'foo'
ORDER BY dt ASC

answered by 04.05.2016 в 17:25

Optimize SQL query problem when comparing by date format

score 3 · Accepted Answer

3

What happens is that dt is an alias and aliases can not be referenced in the WHERE.

This is because the WHERE is executed first than the SELECT.

You can use

    ->where('Year(dateFailure)',$year)

answered by 04.05.2016 / 17:26

source