The "mergeQueries"-function calls $this->getTable() in a DB::raw() and thereby ignoring any check whether a table prefix is in use. This causes error messages on queries on "products"-table with prefix "qs_" like:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'qs_products.accountid' in 'where clause' (SQL: select count() as aggregate from (select qs_products._, (case when LOWER(brand) LIKE xyz then 150 else 0 end) + (case when LOWER(brand) LIKE xyz% then 50 else 0 end) + (case when LOWER(brand) LIKE %xyz% then 10 else 0 end) + (case when LOWER(description) LIKE xyz then 300 else 0 end) + (case when LOWER(description) LIKE xyz% then 100 else 0 end) + (case when LOWER(description) LIKE %xyz% then 20 else 0 end) as relevance from qs_products where qs_products.account_id = 30 and shop_id in (33) group by qs_products.id having relevance > 7.50 order by relevance desc) as products where qs_products.account_id = 30)
Where:
"relevancedesc) as products whereqs_products.account_id` = 30 "
Should be:
"relevancedesc) as qs_products whereqs_products.account_id` = 30 "
Introducing $tableNameRaw and changing the "mergeQueries"-function to the code below solves my issues (in Laravel 4.2):
protected function mergeQueries(Builder $clone, Builder $original) {
$tableNameRaw = DB::getQueryGrammar()->wrapTable($this->getTable());
$original->from(DB::raw("({$clone->toSql()}) as {$tableNameRaw}"));
$original->mergeBindings($clone->getQuery());
}
Fanxd
commented
5 years ago
The "mergeQueries"-function calls $this->getTable() in a DB::raw() and thereby ignoring any check whether a table prefix is in use. This causes error messages on queries on "products"-table with prefix "qs_" like:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'qs_products.accountid' in 'where clause' (SQL: select count() as aggregate from (select
qs_products._, (case when LOWER(brand) LIKE xyz then 150 else 0 end) + (case when LOWER(brand) LIKE xyz% then 50 else 0 end) + (case when LOWER(brand) LIKE %xyz% then 10 else 0 end) + (case when LOWER(description) LIKE xyz then 300 else 0 end) + (case when LOWER(description) LIKE xyz% then 100 else 0 end) + (case when LOWER(description) LIKE %xyz% then 20 else 0 end) as relevance fromqs_productswhereqs_products.account_id= 30 and shop_id in (33) group byqs_products.idhaving relevance > 7.50 order byrelevancedesc) as products whereqs_products.account_id= 30)Where: "relevance
desc) as products whereqs_products.account_id` = 30 "Should be: "relevance
desc) as qs_products whereqs_products.account_id` = 30 "Introducing $tableNameRaw and changing the "mergeQueries"-function to the code below solves my issues (in Laravel 4.2): protected function mergeQueries(Builder $clone, Builder $original) {
Used the code suggestion from Christian Mayer: http://blog.fox21.at/2015/05/06/laravel-5-usage-of-table-names-in-raw-mysql-queries.html