What's the recommended way to construct custom SQL queries? I
specifically want to know how I should get the appropriate table names.
I can get the table name (including any prefixes) of the current model
object by accessing the $this->table variable. But how should I access
the table name of a related table?
Here's an example (this is placed inside the Post model):
$records = $this->query('
SELECT
Post.id,
Post.title,
Post.body,
Post.created,
COUNT(post_id) AS num_comments
FROM posts AS Post
LEFT JOIN post_comments
ON Post.id = post_id
GROUP BY
Post.id
ORDER BY
Post.created DESC');
If I used a query like the one above I would have to change all table
references (posts, post_comments) each time I change the table name or
the global table prefix.
But what's the best way to build these table names?
I came up with the following code which is not a very satisfying
solution:
$postComment = new PostComment();
$records = $this->query('
SELECT
Post.id,
Post.title,
Post.body,
Post.created,
COUNT(post_id) AS num_comments
FROM '.$this->table.' AS Post
LEFT JOIN '.$postComment->table.'
ON Post.id = post_id
GROUP BY
Post.id
ORDER BY
Post.created DESC');
Here I'm constructing a PostComment model just in order to access its
table attribute.
Any alternatives?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" 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
-~----------~----~----~----~------~----~------~--~---
