How to retrieve (& output via twig) one key pair of json object stored i a database column

Question

in commerce, all transactions are written into the database, into the table "commerce_transactions". every transactions will result in a row, where different data is the stored into columns. (id, gateway, amount, ..).

i have a plugin which provides a gateway for a swiss payment provider. When somebody pays over this gateway a column called "response" is filled with a json object, which includes also the amount, the id, and pmethod (basically which card is used).

short example:

{"currency":"CHF","amount":"6000","pmethod":"PFC","status":"success"}

because of a redirect, each transaction results in two rows. i am interested in the row, which has a 01 in the code column, because only this row has the "pmethod" key in the "response" column. (see screenshot)

i get the data with this:

{% set orderFields = craft.query().select('response').from('commerce_transactions').all() %}

but i need only one key from the object, the pmethod. i want to output "PFC", or whatever is written there for outputting it via twig in the confirmation customer email.

how do i get this one key pair?

Answer 1

If you know there's only one row you're interested in, you can execute the query with .one() instead of .array(), this will give you a single result row as an associative array instead of an array of rows. In your case, since you're only interested in a single column, you can also use .scalar() to return that column directly as a string. Once you have the string, you can decode the JSON string using the json_decode filter. Then you can access the value you're interested in.

To handle the case where the result row doesn't exist, check if the query didn't return null or false before attempting to decode the result.

{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .scalar()
%}
{% set orderFields = orderFields ? orderFields|json_decode : null %}
{% set pmethod = orderFields ? orderFields.pmethod : null %}

Query filtering

Your query doesn't apply any conditions or sorting, so it will just fetch ALL transactions (which might be a huge dataset once you have a couple of customers). Or if you're executing the query with .scalar() or .one(), it will give you just one (essentially) random row as the result.

In order to get the row you need, you need to add conditions to the query that uniquely identify that row. For a start, you can filter by rows that have the text 01 in their code field:

{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .where(['code' => '01'])
    .scalar()
%}

Since most of the data in that table is contained in the string as JSON, filtering based on that might be a little difficult. In this case, you can also fetch multiple transactions and then use the filter filter to find the one you need. Something like this:

{% set orderFields = craft.query()
    .select('response')
    .from('commerce_transactions')
    .all()
    |map(row => row | json_decode)
%}
{% set orderFieldWithPaymentInfo = orderFields
    |filter(row => row.authorizationCode == '534711419')
    |first
%}
{% set pmethod = orderFieldWithPaymentInfo.pmethod %}

---

This method is really error-prone and I don't think it's the right approach. Craft Commerce provides services and and hooks to access data (such as transactions) at specific points in the order process, and you should probably use those. It's rarely necessary to bypass those APIs and query the database directly. This is bad practice in particular because then you're tightly coupling your plugin logic to implementation details of Craft Commerce that might change at any point.

Take a look at the documentation for Craft Commerce to find the best way to get the data you're looking for.