1

I want to join the main table code with the product_options value giftcard_created_codes

My query till now

$this->getSelect()->joinLeft(['secondTable' => $this->getTable('sales_order_item')], "secondTable.product_options = main_table.code ", ['secondTable.product_options as product_options']);

product_options value

{"info_buyRequest":{"uenc":"aHR0cDovLzE5Mi4xNjguMS4xODQvZGcvZHJ1bW1vbmQtZ29sZi1lLWdpZnQtY2FyZC5odG1s","product":"76450","selected_configurable_option":"","related_product":"","giftcard_amount":"5","giftcard_sender_name":"test","giftcard_sender_email":"testmscgoriteeps@gmail.com","giftcard_recipient_name":"test22","giftcard_recipient_email":"arjun.cmarix@gmail.com","giftcard_message":"test test gift","qty":"1"},"giftcard_sender_name":"test","giftcard_sender_email":"testmscgoriteeps@gmail.com","giftcard_recipient_name":"test22","giftcard_recipient_email":"arjun.cmarix@gmail.com","giftcard_message":"test test gift","giftcard_lifetime":null,"giftcard_is_redeemable":0,"giftcard_email_template":null,"giftcard_type":"0","email_sent":0,"giftcard_created_codes":["HQE9EMUNTA6N"]}

enter image description here

enter image description here

  • please add structure of your main_table – Hiren Jul 15 at 9:09
  • Added in my question you can verify – Arjun Jul 15 at 9:14
  • i have posted the solution, please accept and upvote if it solve your problem – Hiren Jul 15 at 9:43
0

Try something like below.

$this->getSelect()->joinLeft(
    ['secondTable' => $this->getTable('sales_order_item')],
    new \Zend_Db_Expr("JSON_UNQUOTE(json_extract(secondTable.product_options, '$.giftcard_created_codes[0]')) = main_table.code"),
    ['secondTable.product_options as product_options']);
  • I got error Parse error: syntax error, unexpected '[', expecting ',' or ')' – Arjun Jul 15 at 9:39
  • missed semi colon in second argument, edited the answer. – Yash Shah Jul 15 at 9:40
  • i used your code but it keeps on continuous loading – Arjun Jul 15 at 10:05
  • Can you please print the query and paste the sql here ? also execute that sql in your mysql and check if you get appropriate data or not ? – Yash Shah Jul 15 at 10:07
  • By the way what do you want from sales_order_item ? i dont understand your requirement ... – Yash Shah Jul 15 at 10:09
0
$subquery = new \Zend_Db_Expr("select json_extract(product_options, '$.giftcard_created_codes') from sales_order_item");
$this->getSelect()->join(['sq' => $subquery], "main_table.code = sq.value", []);
  • I got error Parse error: syntax error, unexpected '[', expecting ',' or ')' – Arjun Jul 15 at 9:45
  • i have updated my answer check now – Hiren Jul 15 at 9:52
  • Parse error: syntax error, unexpected '$', expecting ',' or ')' – Arjun Jul 15 at 9:58
  • please check now – Hiren Jul 15 at 10:02
  • Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42000]: Syntax error or access violation: 1064 – Arjun Jul 15 at 10:04

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.