Ttdfjt

I have a domain, static IP address and many devices I'd like to access outside my house. How do I route them?

Why did computer video outputs go from digital to analog, then back to digital?

Bug in Lualatex: not printing characters from calculation

Short story about a group of sci-fi writers sitting around discussing their profession

what to say when a company asks you why someone (a friend) who was fired left?

Why must API keys be kept private?

What is an Eternal Word™?

dos2unix is unable to convert typescript file to unix format

Can GPL and BSD licensed applications be used for government work?

Inverse Colombian Function

Considerations when providing money to one child now, and the other later?

High income and difficulty during interviews

Why are there not any MRI machines available in Interstellar?

What is the best word describing the nature of expiring in a short amount of time, connoting "losing public attention"?

Company requiring me to let them review research from before I was hired

How do changes to your speed that occur on your own turn affect your available movement?

Sextortion with actual password not found in leaks

Grid/table with lots of buttons

Raw curve25519 public key points

Can I pay with HKD in Macau or Shenzhen?

Are rockets faster than airplanes?

Why did NASA use Imperial units?

What would be the side effects on the life of a person becoming indestructible?

Invert Some Switches on a Switchboard

magento2 join query

Magento2 Need to filter grid when value is entered in search box, the column of the data is from another tableExport from the database brand, quantity and size/color varchar: MySQL & EEComplex custom collection join problemHow to add extension attributes to Customer entity in Magento2?How to optimize the query: the product qty bought with each of the productsForeach loop taking long time in magento product collection with limitmagento2 Item (MagentoSalesModelOrder) with the same id “x” already existConfigurable Product MSRPMagento 2.2.5: how to query in observer best practiceMagento 2 : Custom Report with Two Table Join issueMagento 2 Join with ProductRepositoryInterface

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

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"]

magento2 query join

share|improve this question

edited Jul 15 at 13:07

Raj Mohan R

1,69933 silver badges1313 bronze badges

asked Jul 15 at 8:46

ArjunArjun

1,9841010 silver badges2626 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  please add structure of your main_table
  
  – user4536
  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
  
  – user4536
  Jul 15 at 9:43
  

  
  
  
  

add a comment | 

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"]

magento2 query join

share|improve this question

edited Jul 15 at 13:07

Raj Mohan R

1,69933 silver badges1313 bronze badges

asked Jul 15 at 8:46

ArjunArjun

1,9841010 silver badges2626 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  please add structure of your main_table
  
  – user4536
  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
  
  – user4536
  Jul 15 at 9:43
  

  
  
  
  

add a comment | 

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"]

magento2 query join

share|improve this question

edited Jul 15 at 13:07

Raj Mohan R

1,69933 silver badges1313 bronze badges

asked Jul 15 at 8:46

ArjunArjun

1,9841010 silver badges2626 bronze badges

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

"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"]

share|improve this question

edited Jul 15 at 13:07

Raj Mohan R

1,69933 silver badges1313 bronze badges

asked Jul 15 at 8:46

ArjunArjun

1,9841010 silver badges2626 bronze badges

share|improve this question

edited Jul 15 at 13:07

Raj Mohan R

1,69933 silver badges1313 bronze badges

asked Jul 15 at 8:46

ArjunArjun

1,9841010 silver badges2626 bronze badges

edited Jul 15 at 13:07

Raj Mohan R

1,69933 silver badges1313 bronze badges

edited Jul 15 at 13:07

Raj Mohan R

1,69933 silver badges1313 bronze badges

asked Jul 15 at 8:46

ArjunArjun

1,9841010 silver badges2626 bronze badges

asked Jul 15 at 8:46

ArjunArjun

1,9841010 silver badges2626 bronze badges

2 Answers
2

active

oldest

votes

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']);

share|improve this answer

edited Jul 15 at 9:40

answered Jul 15 at 9:36

Yash ShahYash Shah

1,20822 silver badges1212 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 3 more comments

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", []);

share|improve this answer

edited Jul 15 at 10:00

answered Jul 15 at 9:41

user4536user4536

36311 silver badge1515 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got error Parse error: syntax error, unexpected '[', expecting ',' or ')'
  
  – Arjun
  Jul 15 at 9:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i have updated my answer check now
  
  – user4536
  Jul 15 at 9:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Parse error: syntax error, unexpected '$', expecting ',' or ')'
  
  – Arjun
  Jul 15 at 9:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  please check now
  
  – user4536
  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
  

  
  
  
  

 | 
show 1 more comment

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "479"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f282069%2fmagento2-join-query%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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']);

share|improve this answer

edited Jul 15 at 9:40

answered Jul 15 at 9:36

Yash ShahYash Shah

1,20822 silver badges1212 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 3 more comments

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']);

share|improve this answer

edited Jul 15 at 9:40

answered Jul 15 at 9:36

Yash ShahYash Shah

1,20822 silver badges1212 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 3 more comments

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']);

share|improve this answer

edited Jul 15 at 9:40

answered Jul 15 at 9:36

Yash ShahYash Shah

1,20822 silver badges1212 bronze badges

$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']);

share|improve this answer

edited Jul 15 at 9:40

answered Jul 15 at 9:36

Yash ShahYash Shah

1,20822 silver badges1212 bronze badges

answered Jul 15 at 9:36

Yash ShahYash Shah

1,20822 silver badges1212 bronze badges

answered Jul 15 at 9:36

Yash ShahYash Shah

1,20822 silver badges1212 bronze badges

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", []);

share|improve this answer

edited Jul 15 at 10:00

answered Jul 15 at 9:41

user4536user4536

36311 silver badge1515 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got error Parse error: syntax error, unexpected '[', expecting ',' or ')'
  
  – Arjun
  Jul 15 at 9:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i have updated my answer check now
  
  – user4536
  Jul 15 at 9:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Parse error: syntax error, unexpected '$', expecting ',' or ')'
  
  – Arjun
  Jul 15 at 9:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  please check now
  
  – user4536
  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
  

  
  
  
  

 | 
show 1 more comment

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", []);

share|improve this answer

edited Jul 15 at 10:00

answered Jul 15 at 9:41

user4536user4536

36311 silver badge1515 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got error Parse error: syntax error, unexpected '[', expecting ',' or ')'
  
  – Arjun
  Jul 15 at 9:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i have updated my answer check now
  
  – user4536
  Jul 15 at 9:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Parse error: syntax error, unexpected '$', expecting ',' or ')'
  
  – Arjun
  Jul 15 at 9:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  please check now
  
  – user4536
  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
  

  
  
  
  

 | 
show 1 more comment

$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", []);

share|improve this answer

edited Jul 15 at 10:00

answered Jul 15 at 9:41

user4536user4536

36311 silver badge1515 bronze badges

$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", []);

share|improve this answer

edited Jul 15 at 10:00

answered Jul 15 at 9:41

user4536user4536

36311 silver badge1515 bronze badges

answered Jul 15 at 9:41

user4536user4536

36311 silver badge1515 bronze badges

answered Jul 15 at 9:41

user4536user4536

36311 silver badge1515 bronze badges