current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
+1 for using the extensions collection but wouldn't this retrieve all data just to get the tags? – Sander Mangel Dec 10 '13 at 15:01
    
this is the correct answer. nicely done sylvain – philwinkle♦ Dec 10 '13 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 – Sylvain Rayé Dec 10 '13 at 15:14
up vote 3 down vote

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);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.