Trouble with Sprunje

Hi,

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!

Looks like customerid is in the order by clause, but not in select. Might be in another sort? Do you have the same issue with the build in Sprunje (Group or User) ? Might be useful to see the whole Sprunje class then.

You’re trying to use “customerid” in your order by but it isn’t in the select query. When you do a distinct, it only returns the columns in the select statement, so you can’t order by on a column that doesn’t exist.

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:

<?php
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:

<?php

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:

<?php

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!