On Feb 13, 8:47 pm, brian <[email protected]> wrote:
> 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()?
What's bindTo? http://api.cakephp.org/search/bindTo
Try that
http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find
hth,
AD
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
