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

3
  • please add structure of your main_table – Hiren Jul 15 '19 at 9:09
  • Added in my question you can verify – Arjun Jul 15 '19 at 9:14
  • i have posted the solution, please accept and upvote if it solve your problem – Hiren Jul 15 '19 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']);
8
  • I got error Parse error: syntax error, unexpected '[', expecting ',' or ')' – Arjun Jul 15 '19 at 9:39
  • missed semi colon in second argument, edited the answer. – Yash Shah Jul 15 '19 at 9:40
  • i used your code but it keeps on continuous loading – Arjun Jul 15 '19 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 '19 at 10:07
  • By the way what do you want from sales_order_item ? i dont understand your requirement ... – Yash Shah Jul 15 '19 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", []);
6
  • I got error Parse error: syntax error, unexpected '[', expecting ',' or ')' – Arjun Jul 15 '19 at 9:45
  • i have updated my answer check now – Hiren Jul 15 '19 at 9:52
  • Parse error: syntax error, unexpected '$', expecting ',' or ')' – Arjun Jul 15 '19 at 9:58
  • please check now – Hiren Jul 15 '19 at 10:02
  • Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42000]: Syntax error or access violation: 1064 – Arjun Jul 15 '19 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.