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":"[email protected]","giftcard_recipient_name":"test22","giftcard_recipient_email":"[email protected]","giftcard_message":"test test gift","qty":"1"},"giftcard_sender_name":"test","giftcard_sender_email":"[email protected]","giftcard_recipient_name":"test22","giftcard_recipient_email":"[email protected]","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
    Commented Jul 15, 2019 at 9:09
  • Added in my question you can verify
    – Arjun
    Commented Jul 15, 2019 at 9:14
  • i have posted the solution, please accept and upvote if it solve your problem
    – Hiren
    Commented Jul 15, 2019 at 9:43

2 Answers 2

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

Your Answer

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

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