maize-tech/laravel-searchable

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!