1

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) enter image description here

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?

6
  • What is the query returning at the moment? Try putting {% dd orderFields %} in there to see the result of the query. Also, what kind of column is this response, a custom column in a custom table or a regular field of an entry?
    – MoritzLost
    Apr 13 at 8:25
  • its a custom column of a custom table.
    – Henrik
    Apr 13 at 8:26
  • Can you post the result of the dd statement? Also, try using asArray() on the query and/or one() instead of .all().
    – MoritzLost
    Apr 13 at 8:30
  • the result of the query via dd (shortened version): [ 0 => [ 'response' => '{\"amount\":\"3192\",\"pmethod\":\"ECA\",\"status\":\"success\"}' ] ]
    – Henrik
    Apr 13 at 8:30
  • .asArray() gives me an error: Calling unknown method: craft\db\Query::asArray()
    – Henrik
    Apr 13 at 8:35

1 Answer 1

2

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.

7
  • i get en error, "Key "pmethod" for array with keys "amount, currency,..." does not exist. Is this because, in the first row/item in the table, there is no pmethod in the json object? only every second/third row has this pmethod (they have "success" in the status column).
    – Henrik
    Apr 14 at 4:56
  • @Henrik Yes, scalar() returns the first column of the first row that matches so query, so if that one doesn't contain the pmethod field you'll get an error. In this case, either adjust your query to only find the row you're looking for, or execute the query with column() instead. This will return an array of strings, then you can iterate through that to find the row you're looking for.
    – MoritzLost
    Apr 14 at 7:29
  • thanks. getting a bit lost here. with 'column()' you mean the twig filter? so i have to set another variable from orderFields? or how can i adjust the query to get the row i am looking for?
    – Henrik
    Apr 14 at 13:18
  • @Henrik No, I mean execute the query with column(). See this method here. Instead of putting scalar() at the end of your query, use column(). This will return an array of rows that match the query. It's difficult to find a good solution without the larger picture. Maybe post some additional context to your question – where does the data come from, and who puts it there? Why are there multiple rows that match your query? What exactly are you looking for there?
    – MoritzLost
    Apr 14 at 14:00
  • @Henrik You can probably add additional conditions to the query so it only finds exactly the row you're looking for. But without knowing the context I can't tell you what those conditions may be.
    – MoritzLost
    Apr 14 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.