2

I have created a MySQL query for Magento table.

SELECT account_id,
count(session_id) as sessions,

(SELECT count(event_code) from my_custom_table where event_code = 4 
group by account_id) as review_design,

(SELECT count(event_code) from my_custom_table where event_code = 5 
group by account_id) as pdf_created,

(SELECT count(event_code) from my_custom_table where event_code = 2 
group by account_id) as email_sent

FROM my_custom_table 
group by account_id;

I have tried changing this plain query to Magento convention for protected function _prepareCollection() in admin grid. However, I am not able not to convert. I have gone through many answers on the internet, but most are related to the join query.

1

Unless I'm mistaken, I'm not sure the SQL you've got there will do what you've intended. From the looks of it, you want to select all the accounts in my_custom_table, with statistics for each one based on the data (also in my_custom_table) against each account.

The problem I see is the in each the subquery there is no join to account_id in the main SQL, which means for each account_id in your table, you'll get the total count of each event_code regardless of account_id (so all the rows counts will be the same)

A rewrite of the query would also make it much easier to "convert" to the standard magento mechanism, if the query was rewritten to be like this:

SELECT 
   main_table.account_id,
   COUNT(s.session_id) as sessions,
   COUNT(r.event_code) as review_design,
   COUNT(p.event_code) as pdf_created,
   COUNT(e.event_code) as email_sent
FROM 
  my_custom_table main_table
LEFT JOIN my_custom_table s ON main_table.account_id = s.account_id AND s.session_id != ''
LEFT JOIN my_custom_table r ON main_table.account_id = r.account_id AND r.event_code = 4
LEFT JOIN my_custom_table p ON main_table.account_id = p.account_id AND p.event_code = 5
LEFT JOIN my_custom_table e ON main_table.account_id = e.account_id AND e.event_code = 2
GROUP BY main_table.account_id;

Then you could then write code for a collection something like this (untested):

$collection = Mage::getModel('module/model')->getCollection();

$collection->getSelect()
           ->joinLeft(
               array('s' => 'my_custom_table'),
               'main_table.account_id = s.account_id AND s.session_id != ""',
               array('sessions' => 'COUNT(s.session_id)')
           )
           ->joinLeft(
               array('r' => 'my_custom_table'),
               'main_table.account_id = r.account_id AND r.event_code = 4',
               array('review_design' => 'COUNT(r.event_code)')
           )
           ->joinLeft(
               array('p' => 'my_custom_table'),
               'main_table.account_id = p.account_id AND p.event_code = 5',
               array('pdf_created' => 'COUNT(p.event_code)')
           )
           ->joinLeft(
               array('e' => 'my_custom_table'),
               'main_table.account_id = e.account_id AND e.event_code = 2',
               array('email_sent' => 'COUNT(e.event_code)')
           )
        ->group('main_table.account_id');

Which would give you the intended collection.

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.