SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in order clause is ambiguous Laravel 5.5

4
votes

I just don't know what's wrong with my code and why it produces this error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in order clause is ambiguous (SQL: select * from processes inner join bags on processes.bag_id = bags.id where bags.type = Recyclable and date(processes.created_at) = 2018-09-18 00:00:00 order by created_at desc limit 1) and here's my code 

$bag = Bagcollect::join('bags', 'bagcollects.bag_id', '=', 'bags.id')
        ->select('bags.type')
        ->where('bagcollects.bag_id', $request->input('bag_id'))
        ->first();

   //this query produce error
    $processexist = Process::join('bags', 'processes.bag_id', '=', 'bags.id')
        ->where('bags.type', $bag->type)
        ->whereDate('processes.created_at', Carbon::today())
        ->latest()
        ->first();
2
phpmysqllaravellaravel-5.5
you have created_at column in multiple tables (which are being joined here). You need to specify a specific alias/table name to use a particular created_at column for order by – Madhur Bhaiya
Possible duplicate of Codeigniter: Column 'id' in order clause is ambiguous – Madhur Bhaiya
ambiguous column error can only point to SELECT, WHERE, ON (when joining), HAVING, GROUP BY clauses to add to @MadhurBhaiya 's comment – Raymond Nijland

2 Answers

11
votes

You'll need to specify, in latest() the full column. latest('process.created_at') or instead of using latest() use a custom orderBy.

3
votes

Thats because you are querying the 'created_at' column from two tables. You have to specify wich colums you need, for example:

$processexist = Process::join('bags', 'processes.bag_id', '=', 'bags.id')
        ->select('bags.column1', 'bags.columns2')
        ->where('bags.type', $bag->type)
        ->whereDate('processes.created_at', Carbon::today())
        ->latest()
        ->first();