0

This is part of the aw blog module. I have 2 tables.

aw_blog
post_id | tags
1 tag1
2 tag2
3 tag3

aw_blog_post_cat
cat_id | post_id
1 1
1 3
2 3

I have that SQL statement and i am trying to do it with magento way:

SELECT `aw_blog`.`tags` FROM `aw_blog_post_cat` RIGHT JOIN  `aw_blog` on `aw_blog`.`post_id`=`aw_blog_post_cat`.`post_id` WHERE `aw_blog_post_cat`.`cat_id`=1

I tried something like that:

$collection = Mage::getModel('blog/blog')->getCollection()
->getSelect()->joinRight(array("post_cat" => $this->getTable('aw_blog_post_cat')), 'main_table.post_id = post_cat.post_id', array()); 

from that link But with no luck. There is no model implemented for aw_blog_post_cat table

2 Answers 2

5

I didn't test because I don't have the module installed on a Magento installation but I checked the source code and I am not sure if it is latest one. You have in the collection of the Blog class AW_Blog_Model_Mysql4_Blog_Collection a method which helps to filter the content based on the category id

class AW_Blog_Model_Mysql4_Blog_Collection
...
public function addCatFilter($catId)
{
    $this->getSelect()->join(
        array('cat_table' => $this->getTable('post_cat')),
        'main_table.post_id = cat_table.post_id',
        array()
    )
    ->where('cat_table.cat_id = ?', $catId);

    return $this;
}

Then you can use in your code that:

$collection = Mage::getModel('blog/blog')->getCollection()
    ->addCatFilter(1);

$items = $collection->getItems();

So now you get all post items.

2
  • this is the correct answer. nicely done sylvain
    – philwinkle
    Dec 10, 2013 at 15:03
  • @SanderMangel I replaced the collection of cat by the one of the blog which has the same filter. It should display the tags. @philwinkle thx Dec 10, 2013 at 15:14
3

The Fishpig blog has an excellent article about writing custom queries. It explains in detail how to get the query resources and tablenames.

In your case it would be something like this

$resource = Mage::getSingleton('core/resource');
$read = $resource->getConnection('core_read');

$results = $read->fetchAll("SELECT * FROM {$resource->getTableName('blog/post_cat')}"); // change the query to your needs

var_dump($results);

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.