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

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

2 Answers 2

Reset to default
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']);
Improve this answer
8
  • I got error Parse error: syntax error, unexpected '[', expecting ',' or ')'
    – Arjun
    Jul 15, 2019 at 9:39
  • missed semi colon in second argument, edited the answer.
    – Yash Shah
    Jul 15, 2019 at 9:40
  • i used your code but it keeps on continuous loading
    – Arjun
    Jul 15, 2019 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, 2019 at 10:07
  • By the way what do you want from sales_order_item ? i dont understand your requirement ...
    – Yash Shah
    Jul 15, 2019 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", []);
Improve this answer
6
  • I got error Parse error: syntax error, unexpected '[', expecting ',' or ')'
    – Arjun
    Jul 15, 2019 at 9:45
  • i have updated my answer check now
    – Hiren
    Jul 15, 2019 at 9:52
  • Parse error: syntax error, unexpected '$', expecting ',' or ')'
    – Arjun
    Jul 15, 2019 at 9:58
  • please check now
    – Hiren
    Jul 15, 2019 at 10:02
  • Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42000]: Syntax error or access violation: 1064
    – Arjun
    Jul 15, 2019 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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