CakePHP: difference between backtick query and normal query

I have 3 blog tables in my CakePHP application that are linked with a HABTM association following the CakePHP naming conventions: posts - post_tag_links - tags.

I try to get an array with all the posts that are linked to a specific tag (e.g. "design"). This query works for me:

$this->Post->query("
    SELECT
        Post.id, Post.title FROM posts AS Post
    LEFT JOIN
        post_tag_links AS PostTagLink ON Post.id = PostTagLink.post_id
    LEFT JOIN
        tags AS Tag ON Tag.id = PostTagLink.tag_id
    WHERE
        Tag.slug = 'design'
    GROUP BY
        Post.id"
);

CakePHP then generates the following query and gave me 4 results:

SELECT
    Post.id,
    Post.title 
FROM
    posts AS Post 
LEFT JOIN
    post_tag_links AS PostTagLink 
        ON Post.id = PostTagLink.post_id 
LEFT JOIN
    tags AS Tag 
        ON Tag.id = PostTagLink.tag_id 
WHERE
    Tag.slug = 'design' 
GROUP BY
    Post.id

BUT... to do some best practice, it's better to not use the "query" method. So I tried the "find all" method:

$this->Post->find('all', array(
    'fields' => array(
        'Post.id',
        'Post.title'
    ),
    'joins' => array(
        array(
            'table' => 'post_tag_links',
            'alias' => 'PostTagLink',
            'type' => 'LEFT',
            'conditions' => array(
                'Post.id' => 'PostTagLink.post_id'
            )
        ),
        array(
            'table' => 'tags',
            'alias' => 'Tag',
            'type' => 'LEFT',
            'conditions' => array(
                'Tag.id' => 'PostTagLink.tag_id',
            )
        )
    ),
    'conditions' => array(
        'Tag.slug' => 'design'
    ),
    'group' => 'Post.id'
    )
));

CakePHP then generates the following query and gave NO single result:

SELECT
    `Post`.`id`,
    `Post`.`title` 
FROM
    `kattenbelletjes`.`posts` AS `Post` 
LEFT JOIN
    `kattenbelletjes`.`post_tag_links` AS `PostTagLink` 
        ON (
            `Post`.`id` = 'PostTagLink.post_id'
        ) 
LEFT JOIN
    `kattenbelletjes`.`tags` AS `Tag` 
        ON (
            `Tag`.`id` = 'PostTagLink.tag_id'
        ) 
WHERE
    `Tag`.`slug` = 'design' 
GROUP BY
    `Post`.`id

After a lot of trial and error, I discovered the problem is the backticks that CakePHP creates when building up that last query.

My question is: what's the difference between the query with the backticks and the one without the backticks? And how can you leave those backticks in CakePHP?

Thanks ;)

Solution

The backticks most probably aren't the problem, as all they do is escaping the identifiers. This is a pretty easy find btw.

The actual problem is more likely that you've defined the conditions in the wrong way, what you are doing there is creating string literal comparision conditions, ie

`Post`.`id` = 'PostTagLink.post_id'

Comparing the id column value to the string PostTagLink.post_id will of course fail.

The correct way to define identifier comparisons is to supply the conditon fragment as a single value instead of a key => value set, ie

'conditions' => array(
    'Post.id = PostTagLink.post_id'
)

and

'conditions' => array(
    'Tag.id = PostTagLink.tag_id'
)