The situation: for NewslletterController::admin_edit() I need to fetch
all of the Posts that have been made since the previous Newsletter was
published. I've got an SQL view which accomplishes the task but I'd
really like to take advantage of Cake's find(). Currently, I can only
use query().
Newsletter is not associated with Post, so I have to bind them (using
an INNER JOIN) and this is where I've run into a wall.
Here's the relevant SQL:
SET prev_newsletter_id = newsletter_id - 1;
SELECT Post.id,
CASE
WHEN Post.title IS NOT NULL THEN Post.title
ELSE pc.name
END AS title,
Post.body
FROM posts AS Post
LEFT JOIN post_categories as pc ON Post.post_category_id = pc.id
INNER JOIN newsletters AS Newsletter ON Newsletter.id = prev_newsletter_id
WHERE Post.created BETWEEN Newsletter.publish_date AND NOW();
And this is far as I've gotten with setting up a find() call:
$filters = array(
'conditions' => array(
'Post.created BETWEEN Newsletter.publish_date AND NOW()'
),
'fields' => array(
'Post.id',
'CASE WHEN Post.title IS NOT NULL THEN Post.title ELSE
PostCategory.name END AS title',
'Post.body'
),
'contain' => array(
'PostCategory',
'Newsletter' => array(
'fields' => array('Newsletter.id',
'Newsletter.publish_date')
)
)
);
$posts = $this->Newsletter->Post->find('all', $filters);
Obviously, the bind part is missing. I can't figure out which
association I need here, as there's nothing really to connect the two
models.
Also, where and how should I deal with the "Newsletter.id =
prev_newsletter_id" part? In the contain array, or in the bindTo()?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
