How to limit the results from a Left Join in Magento?How do you iterate the resultset of a join?Filter Collection with result of previous collectionProduct Collection for Default CategorySQLSTATE[42S02]: Base table or view not found: 1146 Table 'core_store' doesn't existMagento customer left join not returning all column from customer tableSubselect as main_table on magento collectionProduct List Collection to not show products in Category x unless products ids in?Magento sales_order_grid + join concatenated results from other queryMagento 1.9: How to show instock items first in product listing even after using sortingMagento - Add customer attribute to order grid
Meaning of "SEVERA INDEOVI VAS" from 3rd Century slab
How is the Swiss post e-voting system supposed to work, and how was it wrong?
Co-worker team leader wants to inject his friend's awful software into our development. What should I say to our common boss?
How to make healing in an exploration game interesting
I need to drive a 7/16" nut but am unsure how to use the socket I bought for my screwdriver
2D counterpart of std::array in C++17
Bastion server: use TCP forwarding VS placing private key on server
Making a sword in the stone, in a medieval world without magic
Informing my boss about remarks from a nasty colleague
Using "wallow" verb with object
Replacing Windows 7 security updates with anti-virus?
How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?
Provisioning profile doesn't include the application-identifier and keychain-access-groups entitlements
What does it mean to make a bootable LiveUSB?
Is a lawful good "antagonist" effective?
Old race car problem/puzzle
Is having access to past exams cheating and, if yes, could it be proven just by a good grade?
The use of "touch" and "touch on" in context
Instead of Universal Basic Income, why not Universal Basic NEEDS?
Why using two cd commands in bash script does not execute the second command
What has been your most complicated TikZ drawing?
PTIJ: Who should pay for Uber rides: the child or the parent?
Font with correct density?
Can elves maintain concentration in a trance?
How to limit the results from a Left Join in Magento?
How do you iterate the resultset of a join?Filter Collection with result of previous collectionProduct Collection for Default CategorySQLSTATE[42S02]: Base table or view not found: 1146 Table 'core_store' doesn't existMagento customer left join not returning all column from customer tableSubselect as main_table on magento collectionProduct List Collection to not show products in Category x unless products ids in?Magento sales_order_grid + join concatenated results from other queryMagento 1.9: How to show instock items first in product listing even after using sortingMagento - Add customer attribute to order grid
I have a collection that I need to join with another collection... the problem is the table contains 125k rows and so it times out.
//Fields
$this->addFieldToSelect('id');
$this->addFieldToSelect('subject');
$this->addFieldToSelect('email_address');
$this->addFieldToSelect('mail_id');
$this->addFieldToSelect('date_sent');
$this->addFieldToSelect('customer_id');
//Get latest status
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'main_table.id = me1.email_id',
'event_type as current_status'
);
$this->getSelect()->joinLeft(
array('me2' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'(main_table.id = me2.email_id AND (me1.timestamp < me2.timestamp OR me1.timestamp = me2.timestamp AND me1.id < me2.id))',
false
);
So I need to limit just what comes back in the joinLeft()... how is that possible?
magento-1.9 module collection
edited May 30 '17 at 22:24
Rafael Corrêa Gomes
4,58423265
asked May 30 '17 at 21:41
Adam CoburnAdam Coburn
10817
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a collection that I need to join with another collection... the problem is the table contains 125k rows and so it times out.
//Fields
$this->addFieldToSelect('id');
$this->addFieldToSelect('subject');
$this->addFieldToSelect('email_address');
$this->addFieldToSelect('mail_id');
$this->addFieldToSelect('date_sent');
$this->addFieldToSelect('customer_id');
//Get latest status
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'main_table.id = me1.email_id',
'event_type as current_status'
);
$this->getSelect()->joinLeft(
array('me2' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'(main_table.id = me2.email_id AND (me1.timestamp < me2.timestamp OR me1.timestamp = me2.timestamp AND me1.id < me2.id))',
false
);
So I need to limit just what comes back in the joinLeft()... how is that possible?
magento-1.9 module collection
edited May 30 '17 at 22:24
Rafael Corrêa Gomes
4,58423265
asked May 30 '17 at 21:41
Adam CoburnAdam Coburn
10817
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a collection that I need to join with another collection... the problem is the table contains 125k rows and so it times out.
//Fields
$this->addFieldToSelect('id');
$this->addFieldToSelect('subject');
$this->addFieldToSelect('email_address');
$this->addFieldToSelect('mail_id');
$this->addFieldToSelect('date_sent');
$this->addFieldToSelect('customer_id');
//Get latest status
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'main_table.id = me1.email_id',
'event_type as current_status'
);
$this->getSelect()->joinLeft(
array('me2' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'(main_table.id = me2.email_id AND (me1.timestamp < me2.timestamp OR me1.timestamp = me2.timestamp AND me1.id < me2.id))',
false
);
So I need to limit just what comes back in the joinLeft()... how is that possible?
magento-1.9 module collection
edited May 30 '17 at 22:24
Rafael Corrêa Gomes
4,58423265
asked May 30 '17 at 21:41
Adam CoburnAdam Coburn
10817
I have a collection that I need to join with another collection... the problem is the table contains 125k rows and so it times out.
//Fields
$this->addFieldToSelect('id');
$this->addFieldToSelect('subject');
$this->addFieldToSelect('email_address');
$this->addFieldToSelect('mail_id');
$this->addFieldToSelect('date_sent');
$this->addFieldToSelect('customer_id');
//Get latest status
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'main_table.id = me1.email_id',
'event_type as current_status'
);
$this->getSelect()->joinLeft(
array('me2' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'(main_table.id = me2.email_id AND (me1.timestamp < me2.timestamp OR me1.timestamp = me2.timestamp AND me1.id < me2.id))',
false
);
So I need to limit just what comes back in the joinLeft()... how is that possible?
magento-1.9 module collection
magento-1.9 module collection
edited May 30 '17 at 22:24
Rafael Corrêa Gomes
4,58423265
asked May 30 '17 at 21:41
Adam CoburnAdam Coburn
10817
edited May 30 '17 at 22:24
Rafael Corrêa Gomes
4,58423265
asked May 30 '17 at 21:41
Adam CoburnAdam Coburn
10817
edited May 30 '17 at 22:24
Rafael Corrêa Gomes
4,58423265
edited May 30 '17 at 22:24
Rafael Corrêa Gomes
4,58423265
edited May 30 '17 at 22:24
Rafael Corrêa Gomes
4,58423265
4,58423265
asked May 30 '17 at 21:41
Adam CoburnAdam Coburn
10817
asked May 30 '17 at 21:41
Adam CoburnAdam Coburn
10817
asked May 30 '17 at 21:41
Adam CoburnAdam Coburn
10817
10817
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
At the end you can limit the records you want
$this->getSelect()->limit(20);
answered May 31 '17 at 4:31
PriyankPriyank
5,21241950
The problem is I want all from the main collection, it's the join on the events that has 125k rows that I want to limit
– Adam Coburn
May 31 '17 at 10:45
add a comment |
This work for me:
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'me1.event_type = (SELECT event_type FROM me1 WHERE main_table.id = me1.email_id LIMIT 1)',
'event_type as current_status'
);
but it's got very bad perfomance impact.
Hope some one find a better solution.
answered Jun 29 '17 at 18:12
krybbiokrybbio
607622
add a comment |
I check and understand Mage::getResourceModel('someprovider_mail/event')->getMainTable() is grow day by day i think. limit is not a good solution for this.
I have provide two good solution for this.
Solution 1: : In your query you can narrow down some more based on Date or any other fields.
Solution 2: : Try to create another table what are the fields you need, Write a insert query based on your needs and setup in cron tab run everyday or twice a day and store in created table. This is what all reporting company are storing and retrieve data fast.
Any other best solution also appreciated.
answered Jun 29 '17 at 19:00
Rama Chandran MRama Chandran M
2,73581530
add a 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
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f176697%2fhow-to-limit-the-results-from-a-left-join-in-magento%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
At the end you can limit the records you want
$this->getSelect()->limit(20);
answered May 31 '17 at 4:31
PriyankPriyank
5,21241950
The problem is I want all from the main collection, it's the join on the events that has 125k rows that I want to limit
– Adam Coburn
May 31 '17 at 10:45
add a comment |
At the end you can limit the records you want
$this->getSelect()->limit(20);
answered May 31 '17 at 4:31
PriyankPriyank
5,21241950
The problem is I want all from the main collection, it's the join on the events that has 125k rows that I want to limit
– Adam Coburn
May 31 '17 at 10:45
add a comment |
At the end you can limit the records you want
$this->getSelect()->limit(20);
answered May 31 '17 at 4:31
PriyankPriyank
5,21241950
At the end you can limit the records you want
$this->getSelect()->limit(20);
answered May 31 '17 at 4:31
PriyankPriyank
5,21241950
answered May 31 '17 at 4:31
PriyankPriyank
5,21241950
answered May 31 '17 at 4:31
PriyankPriyank
5,21241950
answered May 31 '17 at 4:31
PriyankPriyank
5,21241950
5,21241950
The problem is I want all from the main collection, it's the join on the events that has 125k rows that I want to limit
– Adam Coburn
May 31 '17 at 10:45
add a comment |
The problem is I want all from the main collection, it's the join on the events that has 125k rows that I want to limit
– Adam Coburn
May 31 '17 at 10:45
The problem is I want all from the main collection, it's the join on the events that has 125k rows that I want to limit
– Adam Coburn
May 31 '17 at 10:45
The problem is I want all from the main collection, it's the join on the events that has 125k rows that I want to limit
– Adam Coburn
May 31 '17 at 10:45
add a comment |
This work for me:
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'me1.event_type = (SELECT event_type FROM me1 WHERE main_table.id = me1.email_id LIMIT 1)',
'event_type as current_status'
);
but it's got very bad perfomance impact.
Hope some one find a better solution.
answered Jun 29 '17 at 18:12
krybbiokrybbio
607622
add a comment |
This work for me:
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'me1.event_type = (SELECT event_type FROM me1 WHERE main_table.id = me1.email_id LIMIT 1)',
'event_type as current_status'
);
but it's got very bad perfomance impact.
Hope some one find a better solution.
answered Jun 29 '17 at 18:12
krybbiokrybbio
607622
add a comment |
This work for me:
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'me1.event_type = (SELECT event_type FROM me1 WHERE main_table.id = me1.email_id LIMIT 1)',
'event_type as current_status'
);
but it's got very bad perfomance impact.
Hope some one find a better solution.
answered Jun 29 '17 at 18:12
krybbiokrybbio
607622
This work for me:
$this->getSelect()->joinLeft(
array('me1' => Mage::getResourceModel('someprovider_mail/event')->getMainTable()),
'me1.event_type = (SELECT event_type FROM me1 WHERE main_table.id = me1.email_id LIMIT 1)',
'event_type as current_status'
);
but it's got very bad perfomance impact.
Hope some one find a better solution.
answered Jun 29 '17 at 18:12
krybbiokrybbio
607622
answered Jun 29 '17 at 18:12
krybbiokrybbio
607622
answered Jun 29 '17 at 18:12
krybbiokrybbio
607622
answered Jun 29 '17 at 18:12
krybbiokrybbio
607622
607622
add a comment |
add a comment |
I check and understand Mage::getResourceModel('someprovider_mail/event')->getMainTable() is grow day by day i think. limit is not a good solution for this.
I have provide two good solution for this.
Solution 1: : In your query you can narrow down some more based on Date or any other fields.
Solution 2: : Try to create another table what are the fields you need, Write a insert query based on your needs and setup in cron tab run everyday or twice a day and store in created table. This is what all reporting company are storing and retrieve data fast.
Any other best solution also appreciated.
answered Jun 29 '17 at 19:00
Rama Chandran MRama Chandran M
2,73581530
add a comment |
I check and understand Mage::getResourceModel('someprovider_mail/event')->getMainTable() is grow day by day i think. limit is not a good solution for this.
I have provide two good solution for this.
Solution 1: : In your query you can narrow down some more based on Date or any other fields.
Solution 2: : Try to create another table what are the fields you need, Write a insert query based on your needs and setup in cron tab run everyday or twice a day and store in created table. This is what all reporting company are storing and retrieve data fast.
Any other best solution also appreciated.
answered Jun 29 '17 at 19:00
Rama Chandran MRama Chandran M
2,73581530
add a comment |
I check and understand Mage::getResourceModel('someprovider_mail/event')->getMainTable() is grow day by day i think. limit is not a good solution for this.
I have provide two good solution for this.
Solution 1: : In your query you can narrow down some more based on Date or any other fields.
Solution 2: : Try to create another table what are the fields you need, Write a insert query based on your needs and setup in cron tab run everyday or twice a day and store in created table. This is what all reporting company are storing and retrieve data fast.
Any other best solution also appreciated.
answered Jun 29 '17 at 19:00
Rama Chandran MRama Chandran M
2,73581530
I check and understand Mage::getResourceModel('someprovider_mail/event')->getMainTable() is grow day by day i think. limit is not a good solution for this.
I have provide two good solution for this.
Solution 1: : In your query you can narrow down some more based on Date or any other fields.
Solution 2: : Try to create another table what are the fields you need, Write a insert query based on your needs and setup in cron tab run everyday or twice a day and store in created table. This is what all reporting company are storing and retrieve data fast.
Any other best solution also appreciated.
answered Jun 29 '17 at 19:00
Rama Chandran MRama Chandran M
2,73581530
answered Jun 29 '17 at 19:00
Rama Chandran MRama Chandran M
2,73581530
answered Jun 29 '17 at 19:00
Rama Chandran MRama Chandran M
2,73581530
answered Jun 29 '17 at 19:00
Rama Chandran MRama Chandran M
2,73581530
2,73581530
add a comment |
add a comment |
Thanks for contributing an answer to Magento Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f176697%2fhow-to-limit-the-results-from-a-left-join-in-magento%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
