Fixes Unknown column 'notes' in 'where clause' when using Rules

[okaufmann]

Hi!

After upgrading to 5.4.10 I encoutered a error when creating new transactions via API.

Example Request:

Click to expand!

curl --location --request POST 'https://firefly.yourdomain.com/api/v1/transactions' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer YOUR TOKEN' \
--data-raw '{
 "transactions": [
     {
         "type": "deposit",
         "date": "2019-09-17",
         "amount": 12.95,
         "description": "Vegetables",
         "category_name": "Groceries",
         "source_name": "Test",
         "destination_name": "Geldbörse"
     }
 ]
}'

Error logs:

Click to expand!

[2020-09-22 23:31:09] local.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'notes' in 'where clause' (SQL: select `transaction_groups`.`id` as `transaction_group_id`, `transaction_groups`.`user_id` as `user_id`, `transaction_groups`.`created_at` as `created_at`, `transaction_groups`.`updated_at` as `updated_at`, `transaction_groups`.`title` as `transaction_group_title`, `transaction_journals`.`id` as `transaction_journal_id`, `transaction_journals`.`transaction_type_id`, `transaction_journals`.`description`, `transaction_journals`.`date`, `transaction_journals`.`order`, `transaction_types`.`type` as `transaction_type_type`, `source`.`id` as `source_transaction_id`, `source`.`account_id` as `source_account_id`, `source`.`reconciled`, `source`.`amount` as `amount`, `source`.`transaction_currency_id` as `currency_id`, `currency`.`code` as `currency_code`, `currency`.`name` as `currency_name`, `currency`.`symbol` as `currency_symbol`, `currency`.`decimal_places` as `currency_decimal_places`, `source`.`foreign_amount` as `foreign_amount`, `source`.`foreign_currency_id` as `foreign_currency_id`, `foreign_currency`.`code` as `foreign_currency_code`, `foreign_currency`.`name` as `foreign_currency_name`, `foreign_currency`.`symbol` as `foreign_currency_symbol`, `foreign_currency`.`decimal_places` as `foreign_currency_decimal_places`, `destination`.`account_id` as `destination_account_id`, `source_account`.`name` as `source_account_name`, `source_account`.`iban` as `source_account_iban`, `source_account_type`.`type` as `source_account_type`, `dest_account`.`name` as `destination_account_name`, `dest_account`.`iban` as `destination_account_iban`, `dest_account_type`.`type` as `destination_account_type`, `categories`.`id` as `category_id`, `categories`.`name` as `category_name`, `budgets`.`id` as `budget_id`, `budgets`.`name` as `budget_name`, `notes`.`text` as `notes` from `transaction_journals` left join `transaction_groups` on `transaction_journals`.`transaction_group_id` = `transaction_groups`.`id` left join `transactions` as `source` on `source`.`transaction_journal_id` = `transaction_journals`.`id` and `source`.`amount` < 0 left join `transactions` as `destination` on `destination`.`transaction_journal_id` = `transaction_journals`.`id` and `destination`.`amount` > 0 left join `transaction_types` on `transaction_types`.`id` = `transaction_journals`.`transaction_type_id` left join `transaction_currencies` as `currency` on `currency`.`id` = `source`.`transaction_currency_id` left join `transaction_currencies` as `foreign_currency` on `foreign_currency`.`id` = `source`.`foreign_currency_id` left join `accounts` as `source_account` on `source_account`.`id` = `source`.`account_id` left join `account_types` as `source_account_type` on `source_account_type`.`id` = `source_account`.`account_type_id` left join `accounts` as `dest_account` on `dest_account`.`id` = `destination`.`account_id` left join `account_types` as `dest_account_type` on `dest_account_type`.`id` = `dest_account`.`account_type_id` left join `category_transaction_journal` on `category_transaction_journal`.`transaction_journal_id` = `transaction_journals`.`id` left join `categories` on `category_transaction_journal`.`category_id` = `categories`.`id` left join `budget_transaction_journal` on `budget_transaction_journal`.`transaction_journal_id` = `transaction_journals`.`id` left join `budgets` on `budget_transaction_journal`.`budget_id` = `budgets`.`id` left join `notes` on `notes`.`noteable_id` = `transaction_journals`.`id` and `notes`.`noteable_type` = FireflyIII\Models\TransactionJournal where `transaction_journals`.`user_id` = 1 and `transaction_journals`.`user_id` is not null and `transaction_groups`.`deleted_at` is null and `transaction_journals`.`deleted_at` is null and `source`.`deleted_at` is null and `destination`.`deleted_at` is null and `notes` LIKE %swica% and `transaction_journals`.`id` in (1025) and `transaction_journals`.`deleted_at` is null order by `transaction_journals`.`date` desc, `transaction_journals`.`order` asc, `transaction_journals`.`id` desc, `transaction_journals`.`description` desc, `source`.`amount` desc) {"userId":1,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'notes' in 'where clause' (SQL: select `transaction_groups`.`id` as `transaction_group_id`, `transaction_groups`.`user_id` as `user_id`, `transaction_groups`.`created_at` as `created_at`, `transaction_groups`.`updated_at` as `updated_at`, `transaction_groups`.`title` as `transaction_group_title`, `transaction_journals`.`id` as `transaction_journal_id`, `transaction_journals`.`transaction_type_id`, `transaction_journals`.`description`, `transaction_journals`.`date`, `transaction_journals`.`order`, `transaction_types`.`type` as `transaction_type_type`, `source`.`id` as `source_transaction_id`, `source`.`account_id` as `source_account_id`, `source`.`reconciled`, `source`.`amount` as `amount`, `source`.`transaction_currency_id` as `currency_id`, `currency`.`code` as `currency_code`, `currency`.`name` as `currency_name`, `currency`.`symbol` as `currency_symbol`, `currency`.`decimal_places` as `currency_decimal_places`, `source`.`foreign_amount` as `foreign_amount`, `source`.`foreign_currency_id` as `foreign_currency_id`, `foreign_currency`.`code` as `foreign_currency_code`, `foreign_currency`.`name` as `foreign_currency_name`, `foreign_currency`.`symbol` as `foreign_currency_symbol`, `foreign_currency`.`decimal_places` as `foreign_currency_decimal_places`, `destination`.`account_id` as `destination_account_id`, `source_account`.`name` as `source_account_name`, `source_account`.`iban` as `source_account_iban`, `source_account_type`.`type` as `source_account_type`, `dest_account`.`name` as `destination_account_name`, `dest_account`.`iban` as `destination_account_iban`, `dest_account_type`.`type` as `destination_account_type`, `categories`.`id` as `category_id`, `categories`.`name` as `category_name`, `budgets`.`id` as `budget_id`, `budgets`.`name` as `budget_name`, `notes`.`text` as `notes` from `transaction_journals` left join `transaction_groups` on `transaction_journals`.`transaction_group_id` = `transaction_groups`.`id` left join `transactions` as `source` on `source`.`transaction_journal_id` = `transaction_journals`.`id` and `source`.`amount` < 0 left join `transactions` as `destination` on `destination`.`transaction_journal_id` = `transaction_journals`.`id` and `destination`.`amount` > 0 left join `transaction_types` on `transaction_types`.`id` = `transaction_journals`.`transaction_type_id` left join `transaction_currencies` as `currency` on `currency`.`id` = `source`.`transaction_currency_id` left join `transaction_currencies` as `foreign_currency` on `foreign_currency`.`id` = `source`.`foreign_currency_id` left join `accounts` as `source_account` on `source_account`.`id` = `source`.`account_id` left join `account_types` as `source_account_type` on `source_account_type`.`id` = `source_account`.`account_type_id` left join `accounts` as `dest_account` on `dest_account`.`id` = `destination`.`account_id` left join `account_types` as `dest_account_type` on `dest_account_type`.`id` = `dest_account`.`account_type_id` left join `category_transaction_journal` on `category_transaction_journal`.`transaction_journal_id` = `transaction_journals`.`id` left join `categories` on `category_transaction_journal`.`category_id` = `categories`.`id` left join `budget_transaction_journal` on `budget_transaction_journal`.`transaction_journal_id` = `transaction_journals`.`id` left join `budgets` on `budget_transaction_journal`.`budget_id` = `budgets`.`id` left join `notes` on `notes`.`noteable_id` = `transaction_journals`.`id` and `notes`.`noteable_type` = FireflyIII\\Models\\TransactionJournal where `transaction_journals`.`user_id` = 1 and `transaction_journals`.`user_id` is not null and `transaction_groups`.`deleted_at` is null and `transaction_journals`.`deleted_at` is null and `source`.`deleted_at` is null and `destination`.`deleted_at` is null and `notes` LIKE %swica% and `transaction_journals`.`id` in (1025) and `transaction_journals`.`deleted_at` is null order by `transaction_journals`.`date` desc, `transaction_journals`.`order` asc, `transaction_journals`.`id` desc, `transaction_journals`.`description` desc, `source`.`amount` desc) at firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671)
[stacktrace]
#0 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\\Database\\Connection->runQueryCallback('select `transac...', Array, Object(Closure))
#1 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Connection.php(339): Illuminate\\Database\\Connection->run('select `transac...', Array, Object(Closure))
#2 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2260): Illuminate\\Database\\Connection->select('select `transac...', Array, true)
#3 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2248): Illuminate\\Database\\Query\\Builder->runSelect()
#4 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2743): Illuminate\\Database\\Query\\Builder->Illuminate\\Database\\Query\\{closure}()
#5 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2249): Illuminate\\Database\\Query\\Builder->onceWithColumns(Array, Object(Closure))
#6 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(547): Illuminate\\Database\\Query\\Builder->get(Array)
#7 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(531): Illuminate\\Database\\Eloquent\\Builder->getModels(Array)
#8 firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php(155): Illuminate\\Database\\Eloquent\\Builder->get(Array)
#9 firefly-iii/app/Helpers/Collector/GroupCollector.php(167): Illuminate\\Database\\Eloquent\\Relations\\Relation->get(Array)
#10 firefly-iii/app/Helpers/Collector/GroupCollector.php(190): FireflyIII\\Helpers\\Collector\\GroupCollector->getGroups()
#11 firefly-iii/app/Support/Search/OperatorQuerySearch.php(200): FireflyIII\\Helpers\\Collector\\GroupCollector->getPaginatedGroups()
#12 firefly-iii/app/TransactionRules/Engine/SearchRuleEngine.php(307): FireflyIII\\Support\\Search\\OperatorQuerySearch->searchTransactions()
#13 firefly-iii/app/TransactionRules/Engine/SearchRuleEngine.php(221): FireflyIII\\TransactionRules\\Engine\\SearchRuleEngine->findNonStrictRule(Object(FireflyIII\\Models\\Rule))
#14 firefly-iii/app/TransactionRules/Engine/SearchRuleEngine.php(133): FireflyIII\\TransactionRules\\Engine\\SearchRuleEngine->fireNonStrictRule(Object(FireflyIII\\Models\\Rule))
#15 firefly-iii/app/TransactionRules/Engine/SearchRuleEngine.php(98): FireflyIII\\TransactionRules\\Engine\\SearchRuleEngine->fireRule(Object(FireflyIII\\Models\\Rule))
#16 firefly-iii/app/Handlers/Events/StoredGroupEventHandler.php(70): FireflyIII\\TransactionRules\\Engine\\SearchRuleEngine->fire()
#17 [internal function]: FireflyIII\\Handlers\\Events\\StoredGroupEventHandler->processRules(Object(FireflyIII\\Events\\StoredTransactionGroup))
#18 firefly-iii/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php(400): call_user_func_array(Array, Array)
#19 firefly-iii/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php(226): Illuminate\\Events\\Dispatcher->Illuminate\\Events\\{closure}('FireflyIII\\\\Even...', Array)
#20 firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/helpers.php(476): Illuminate\\Events\\Dispatcher->dispatch('FireflyIII\\\\Even...')
#21 firefly-iii/app/Api/V1/Controllers/TransactionController.php(330): event(Object(FireflyIII\\Events\\StoredTransactionGroup))
#22 [internal function]: FireflyIII\\Api\\V1\\Controllers\\TransactionController->store(Object(FireflyIII\\Api\\V1\\Requests\\TransactionStoreRequest))
#23 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(54): call_user_func_array(Array, Array)
#24 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(45): Illuminate\\Routing\\Controller->callAction('store', Array)
#25 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Route.php(239): Illuminate\\Routing\\ControllerDispatcher->dispatch(Object(Illuminate\\Routing\\Route), Object(FireflyIII\\Api\\V1\\Controllers\\TransactionController), 'store')
#26 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Route.php(196): Illuminate\\Routing\\Route->runController()
#27 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(685): Illuminate\\Routing\\Route->run()
#28 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(128): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#29 firefly-iii/app/Api/V1/Controllers/TransactionController.php(85): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#30 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): FireflyIII\\Api\\V1\\Controllers\\TransactionController->FireflyIII\\Api\\V1\\Controllers\\{closure}(Object(Illuminate\\Http\\Request), Object(Closure))
#31 firefly-iii/app/Api/V1/Controllers/Controller.php(63): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#32 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(149): FireflyIII\\Api\\V1\\Controllers\\Controller->FireflyIII\\Api\\V1\\Controllers\\{closure}(Object(Illuminate\\Http\\Request), Object(Closure))
#33 firefly-iii/app/Http/Middleware/Binder.php(79): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#34 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): FireflyIII\\Http\\Middleware\\Binder->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#35 firefly-iii/app/Http/Middleware/Authenticate.php(74): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#36 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): FireflyIII\\Http\\Middleware\\Authenticate->handle(Object(Illuminate\\Http\\Request), Object(Closure), 'api')
#37 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#38 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(687): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#39 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(662): Illuminate\\Routing\\Router->runRouteWithinStack(Object(Illuminate\\Routing\\Route), Object(Illuminate\\Http\\Request))
#40 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(628): Illuminate\\Routing\\Router->runRoute(Object(Illuminate\\Http\\Request), Object(Illuminate\\Routing\\Route))
#41 firefly-iii/vendor/laravel/framework/src/Illuminate/Routing/Router.php(617): Illuminate\\Routing\\Router->dispatchToRoute(Object(Illuminate\\Http\\Request))
#42 firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(165): Illuminate\\Routing\\Router->dispatch(Object(Illuminate\\Http\\Request))
#43 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(128): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}(Object(Illuminate\\Http\\Request))
#44 firefly-iii/vendor/barryvdh/laravel-debugbar/src/Middleware/InjectDebugbar.php(67): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#45 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Barryvdh\\Debugbar\\Middleware\\InjectDebugbar->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#46 firefly-iii/app/Http/Middleware/InstallationId.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#47 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): FireflyIII\\Http\\Middleware\\InstallationId->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#48 firefly-iii/vendor/fideloper/proxy/src/TrustProxies.php(57): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#49 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Fideloper\\Proxy\\TrustProxies->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#50 firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#51 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#52 firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#53 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#54 firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#55 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#56 firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(63): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#57 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\\Foundation\\Http\\Middleware\\CheckForMaintenanceMode->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#58 firefly-iii/app/Http/Middleware/SecureHeaders.php(51): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#59 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): FireflyIII\\Http\\Middleware\\SecureHeaders->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#60 firefly-iii/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#61 firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(140): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#62 firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(109): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter(Object(Illuminate\\Http\\Request))
#63 firefly-iii/public/index.php(76): Illuminate\\Foundation\\Http\\Kernel->handle(Object(Illuminate\\Http\\Request))
#64 /Users/okaufmann/.composer/vendor/laravel/valet/server.php(191): require('/Users/okaufman...')
#65 {main}

Changes in this pull request:

• Fixes filtering for new polymorphic notes table's text colums when using Rules (As described above).

@JC5

[sonarcloud]

SonarCloud Quality Gate failed.

0 Bugs
0 Vulnerabilities (and 0 Security Hotspots to review)
1 Code Smell

No Coverage information
21.5% Duplication

[JC5]

Nice! Merged. Will be part of the next release.