3

I have a db query which looks like

SELECT  f.forum_id,
 t.topic_id,
 MAX(p.post_id) AS `maxpostid`,
 p.admin_user_id,
 p.user_id,
 t2.topicscount,
 SUM(Maxp.postcount) AS postscount,
 CONCAT(au.firstname,' ',au.lastname) AS adminname, 
 fu.nick_name AS nickname,
 CONCAT(ce1.value,' ',ce2.value) AS fullname 

FROM    my_forum AS f        

  LEFT JOIN my_topic AS t
  ON f.forum_id = t.forum_id 

  LEFT JOIN
   (SELECT  topic_id, 
   MAX(post_id) AS post_id,
   COUNT(*) AS postcount
  FROM my_post 
  GROUP BY topic_id
    ) AS Maxp
  ON Maxp.topic_id = t.topic_id 

  LEFT JOIN my_post AS p
  ON p.post_id = Maxp.post_id

  LEFT JOIN admin_user AS au
  ON au.user_id = p.admin_user_id

  LEFT JOIN my_user AS fu
  ON fu.user_id = p.user_id

  LEFT JOIN customer_entity_varchar AS ce1
  ON ce1.entity_id = p.user_id
  AND ce1.attribute_id = 1

  LEFT JOIN customer_entity_varchar AS ce2
  ON ce2.entity_id = p.user_id
  AND ce2.attribute_id = 2

 LEFT JOIN
 (SELECT  forum_id, COUNT(*) AS topicscount
     FROM    my_topic
     GROUP BY forum_id
 ) AS t2
 ON t2.forum_id = f.forum_id

WHERE   forum_status = '1'
GROUP BY f.forum_id

I want to write it with magento collection or convert custom query to collection, is it possible ?

I dont know how to write joins like 

LEFT JOIN
( SELECT  topic_id, 
    MAX(post_id) AS post_id,
    COUNT(*) AS postcount
  FROM my_post 
  GROUP BY topic_id
) AS Maxp
    ON Maxp.topic_id = t.topic_id

in magento, because magento write joins as 

$col->getSelect()->join(array('t'  => 'topic'),'main_f.forum_id = t.forum_id');

how to write it with sub query ?

Thanks

Ahmed
  • 651
  • 17
  • 42

2 Answers2

1

I didn't tested this code, but it should be something like:

$subquery = new Zend_Db_Expr('SELECT my_post.topic_id, MAX(my_post.post_id) AS post_id, COUNT(*) AS postcount FROM my_post GROUP BY my_post.topic_id');
$collection->getSelect()->joinLeft(array('t'  => 'topic'), 'Maxp.topic_id = t.topic_id', array('Maxp' => $subquery));
ToxaBes
  • 1,587
  • 8
  • 17
1

In Magento2, will write sub query as

$subquery = new \Zend_Db_Expr('SELECT my_post.topic_id, MAX(my_post.post_id) AS post_id, COUNT(*) AS postcount FROM my_post GROUP BY my_post.topic_id');
$sql = $collection->getSelect()->from(array('f'  => 'my_forum'), array('f.forum_id'));
$sql->columns(array('Maxp' => $subquery));
//to debug
$sql->__toString();exit;
Venkat
  • 243
  • 2
  • 11