Trouble with Sprunje - Is this a Bug? - UserFrosting Forums | Modern PHP user login and management f

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

飘逸的野马 · API Platform | Filters· 2 周前 ·

精明的显示器 · API Platform | ...· 2 周前 ·

爱笑的瀑布 · Filters - Doctrine ...· 2 周前 ·

聪明伶俐的沙发 · Code Coverage = ...· 4 周前 ·

寂寞的警车 · Data Retrieval And ...· 1 月前 ·

八块腹肌的冲锋衣 · 如何从React Native ...· 2 天前 ·

风流倜傥的匕首 · 朱竹清的真实身份是什么 - 腾讯动漫· 1 周前 ·

俊秀的凳子 · 梦幻西游手游输出类伙伴详解推荐 ...· 2 周前 ·

伤情的匕首 · 河南省文化和旅游厅关于公布河南省第十四届群星 ...· 3 月前 ·

瘦瘦的海豚 · Proof of Concept ...· 3 月前 ·

I already did some small projects in the past with UF 0.3, 4.0 and 4.1 (all of them using MariaDB) and I especially liked the idea of Sprunjes - it worked perfectly w/o any hassle.

I recently started a new project based on UF 4.4.4 but this time using PostgreSQL 13.2 as database system and when running a very simple Sprunje I encounter this error message:

SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

This error message appears when accessing the table through its own route as well as when accessing the Sprunje directly.

The base query is very simple:

protected function baseQuery()
$instance = new CustomerDataCompany();
return $instance->newQuery();

The corresponding model is working fine, I can read and write to that specific table.

Has anyone already came across this or a similar problem and is there maybe an “off-the-shelf” solution for this?

My setup:

NGINX

PHP-FPM 7.4

PostgreSQL 13.2

Redis 6.2

If you need more information, please let me know!
Thanks a lot in advance!

Kind regards,
Marcus

It might be worth enabling debug.queries config to see the exact query made to the database. The error message doesn’t provide much info, but it looks like to me to be similar issue than GroupBy on MySQL. You might need to add a list of required column in the select.

The only place UF use distinct() would be here: https://github.com/userfrosting/UserFrosting/blob/c92bf0b14f43cd53771d782f03c7b61c996ce814/app/sprinkles/core/src/Sprunje/Sprunje.php#L543

…which already define the column for the select. And that line didn’t change in the last 4 years…

Having the full stack trace of the error might be helpful too if you can share it.

Thnx for your reply, here is the SQL statement from the PG logs:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list at character 68
STATEMENT: select distinct “companyname” from “customerdata_company” order by “customerid” asc, “companyname” asc

(i just removed the timestamps)

And here is the stacktrace:

Illuminate\Database\QueryException thrown with message “SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: …companyname” from “customerdata_company” order by “customeri…
^ (SQL: select distinct “companyname” from “customerdata_company” order by “customerid” asc, “companyname” asc)”

Stacktrace:
#38 Illuminate\Database\QueryException in /home/mtoth/uf/app/vendor/illuminate/database/Connection.php:664
#37 Doctrine\DBAL\Driver\PDO\Exception in /home/mtoth/uf/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
#36 Doctrine\DBAL\Driver\PDO\Exception:new in /home/mtoth/uf/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:114
#35 PDOException in /home/mtoth/uf/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:112
#34 PDOStatement:execute in /home/mtoth/uf/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:112
#33 Doctrine\DBAL\Driver\PDOStatement:execute in /home/mtoth/uf/app/vendor/illuminate/database/Connection.php:330
#32 Illuminate\Database\Connection:Illuminate\Database{closure} in /home/mtoth/uf/app/vendor/illuminate/database/Connection.php:657
#31 Illuminate\Database\Connection:runQueryCallback in /home/mtoth/uf/app/vendor/illuminate/database/Connection.php:624
#30 Illuminate\Database\Connection:run in /home/mtoth/uf/app/vendor/illuminate/database/Connection.php:333
#29 Illuminate\Database\Connection:select in /home/mtoth/uf/app/vendor/illuminate/database/Query/Builder.php:2130
#28 Illuminate\Database\Query\Builder:runSelect in /home/mtoth/uf/app/sprinkles/core/src/Database/Builder.php:115
#27 UserFrosting\Sprinkle\Core\Database\Builder:get in /home/mtoth/uf/app/vendor/illuminate/database/Eloquent/Builder.php:521
#26 Illuminate\Database\Eloquent\Builder:getModels in /home/mtoth/uf/app/vendor/illuminate/database/Eloquent/Builder.php:505
#25 Illuminate\Database\Eloquent\Builder:get in /home/mtoth/uf/app/sprinkles/core/src/Sprunje/Sprunje.php:543
#24 UserFrosting\Sprinkle\Core\Sprunje\Sprunje:getColumnValues in /home/mtoth/uf/app/sprinkles/core/src/Sprunje/Sprunje.php:337
#23 UserFrosting\Sprinkle\Core\Sprunje\Sprunje:getListable in /home/mtoth/uf/app/sprinkles/core/src/Sprunje/Sprunje.php:225
#22 UserFrosting\Sprinkle\Core\Sprunje\Sprunje:getArray in /home/mtoth/uf/app/sprinkles/core/src/Sprunje/Sprunje.php:202
#21 UserFrosting\Sprinkle\Core\Sprunje\Sprunje:toResponse in /home/mtoth/uf/app/sprinkles/tools/src/Controller/CustomerDataController.php:487
#20 UserFrosting\Sprinkle\Tools\Controller\CustomerDataController:getCustomerDataKundeSearch in /home/mtoth/uf/app/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php:40
#19 call_user_func in /home/mtoth/uf/app/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php:40
#18 Slim\Handlers\Strategies\RequestResponse:__invoke in /home/mtoth/uf/app/vendor/slim/slim/Slim/Route.php:281
#17 Slim\Route:__invoke in /home/mtoth/uf/app/sprinkles/account/src/Authenticate/AuthGuard.php:53
#16 UserFrosting\Sprinkle\Account\Authenticate\AuthGuard:__invoke in /home/mtoth/uf/app/vendor/slim/slim/Slim/DeferredCallable.php:57
#15 call_user_func_array in /home/mtoth/uf/app/vendor/slim/slim/Slim/DeferredCallable.php:57
#14 Slim\DeferredCallable:__invoke in /home/mtoth/uf/app/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
#13 call_user_func in /home/mtoth/uf/app/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
#12 Slim\Route:Slim{closure} in /home/mtoth/uf/app/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:117
#11 Slim\Route:callMiddlewareStack in /home/mtoth/uf/app/vendor/slim/slim/Slim/Route.php:268
#10 Slim\Route:run in /home/mtoth/uf/app/vendor/slim/slim/Slim/App.php:503
#9 Slim\App:__invoke in /home/mtoth/uf/app/vendor/slim/csrf/src/Guard.php:171
#8 Slim\Csrf\Guard:__invoke in /home/mtoth/uf/app/vendor/slim/slim/Slim/DeferredCallable.php:57
#7 call_user_func_array in /home/mtoth/uf/app/vendor/slim/slim/Slim/DeferredCallable.php:57
#6 Slim\DeferredCallable:__invoke in /home/mtoth/uf/app/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
#5 call_user_func in /home/mtoth/uf/app/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
#4 Slim\App:Slim{closure} in /home/mtoth/uf/app/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:117
#3 Slim\App:callMiddlewareStack in /home/mtoth/uf/app/vendor/slim/slim/Slim/App.php:392
#2 Slim\App:process in /home/mtoth/uf/app/vendor/slim/slim/Slim/App.php:297
#1 Slim\App:run in /home/mtoth/uf/app/system/UserFrosting.php:97
#0 UserFrosting\System\UserFrosting:run in /home/mtoth/uf/public/index.php:24

If you need any more information, please let me know!

Yep, I understand why Postgres is throwing this error, but atm I have no idea “how to get” the necessary fields into the select clause.

I am not experiencing this problem with the built-in Sprunjes, which makes me almost certain that this problem occurs on my side (=my code).

Here is my Sprunje class:

namespace UserFrosting\Sprinkle\Tools\Sprunje; use UserFrosting\Sprinkle\Core\Facades\Debug; use UserFrosting\Sprinkle\Core\Sprunje\Sprunje; use UserFrosting\Sprinkle\Tools\Database\Models\CustomerDataCompany; class CustomerDataCompanySprunje extends Sprunje protected $name = 'customerdata_company'; protected $sortable = [ 'customerid', 'companyname', 'remarks', 'group', 'created_at', 'updated_at' protected $filterable = [ 'customerid', 'companyname', 'remarks', 'group', 'created_at', 'updated_at' //protected $listable = []; protected $listable = [ "customerid", "companyname", "remarks", 'group', 'created_at', 'updated_at' * Set the initial query used by your Sprunje. protected function baseQuery() $instance = new CustomerDataCompany(); // Alternatively, if you have defined a class mapping, you can use the classMapper: // $instance = $this->classMapper->createInstance('owl'); return $instance->newQuery();

The Migration class:

namespace UserFrosting\Sprinkle\Tools\Database\Migrations; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Schema\Builder; use UserFrosting\Sprinkle\Core\Database\Migration; class CustomerDataCompanyTable extends Migration public function up() if (!$this->schema->hasTable('customerdata_company')) { $this->schema->create('customerdata_company', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('customerid')->unique(); $table->string('companyname'); $table->string('remarks'); $table->string('group'); $table->timestamps(); public function down() $this->schema->drop('customerdata_company');

The Model class:

namespace UserFrosting\Sprinkle\Tools\Database\Models; use Illuminate\Database\Capsule\Manager as Capsule; use UserFrosting\Sprinkle\Core\Database\Models\Model; class CustomerDataCompany extends Model * @var string The name of the table for the current model. protected $table = 'customerdata_company'; protected $fillable = [ 'customerid', 'companyname', 'remarks', 'group', 'created_at', 'updated_at' * @var bool Enable timestamps for this class. public $timestamps = true;

Controller Class used for Sprunje:

public function getCustomerDataSearch($request, $response, $args)
    // GET parameters
    $params = $request->getQueryParams();
    // access controlled page
    $authorizer = $this->ci->authorizer;
    $currentUser = $this->ci->currentUser;
    $currentGroup = $currentUser->group_id;
    // alert stream
    $ms = $this->ci->alerts;
    if (!$authorizer->checkAccess($currentUser, 'view_page')) {
            throw new ForbiddenException();
    /** @var UserFrosting\Sprinkle\Core\Util\ClassMapper $classMapper */
    $classMapper = $this->ci->classMapper;
    $sprunje = new CustomerDataCompanySprunje($classMapper, $params);
    return $sprunje->toResponse($response);
Thanks for your help!