Integrity constraint violation: 1052 Column 'created_at' in order clause is ambiguous
djurovicigoor opened this issue · 5 comments
Getting "Integrity constraint violation: 1052 Column 'created_at' in order clause is ambiguous" when trying to use search with belongsTo() relation
Laravel 8.41.0
PHP 8
Bellow is my model
class Transaction extends Model
{
use HasSearch;
use HasUser;
use HasFactory;
/**
* Get the model's searchable attributes.
*
* @return array
*/
public function getSearchableAttributes(): array
{
return [
'type' => 2,
'notes' => 10,
'amount' => 9,
'customer.name',
'paymentType.name',
];
}
/**
* Get the Customer that owns the Transaction.
*/
public function customer()
{
return $this->belongsTo(Customer::class)->withTrashed();
}
/**
* Get the PaymentMethod that owns the Transaction.
*/
public function paymentType()
{
return $this->belongsTo(PaymentType::class);
}
}
Hi!
I just tested locally with a sample model and searched through a belongsTo relation, but couldn't reproduce your issue.
Could you please paste the eloquent query you are running when performing the search and, if possible, paste the executed SQL query directly?
Thank you!
select transactions
.id
from transactions
left join customers
on transactions
.customer_id
= customers
.id
and customers
.deleted_at
is null where (LOWER(transactions.type) LIKE %mad% or LOWER(transactions.notes) LIKE %mad% or LOWER(transactions.amount) LIKE %mad% or LOWER(customers.name) LIKE %mad%) order by (select (CASE WHEN (LOWER(transactions.type) LIKE %mad%) THEN 2 ELSE 0 END)+(CASE WHEN (LOWER(transactions.notes) LIKE %mad%) THEN 10 ELSE 0 END)+(CASE WHEN (LOWER(transactions.amount) LIKE %mad%) THEN 9 ELSE 0 END)+(CASE WHEN (LOWER(customers.name) LIKE %mad%) THEN 1 ELSE 0 END) from transactions
as sw
left join customers
on sw
.customer_id
= customers
.id
and customers
.deleted_at
is null where sw
.id
= transactions
.id
limit 1) desc, created_at
desc)
Alright, got it.
It seems like you are performing an additional order by
with the created_at
column while searching, and it is failing because that column exists both in transactions
and customers
tables. Are you perhaps applying a global scope somewhere?
The solution would be appending the table name to the custom order by
(you can just try with $query->getModel()->getQualifiedCreatedAtColumn()
).
Actually, this helped!!!
I am using a couple of global scopes and one of them is NewestScope
class NewestScope implements Scope
{
/**
* Apply the scope to a given Eloquent query builder.
*
* @param Builder $builder
* @param Model $model
*
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->orderByDesc($builder->getModel()->getQualifiedCreatedAtColumn());
}
}
Thank you @riccardodallavia
Happy it helped!