当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Subquery in cakePHP to lookup in other table

问题描述:

I recently started experimenting with CakePHP. I used this query in "normal" PHP and need to convert it to a CakePHP find. However I can't seem to make it work!

SELECT *,

(SELECT name FROM `users` WHERE `users`.`id`=`products`.`creator`) AS creatorname

FROM `products`

WHERE `products`.`ID`='2'

It's a basic set up with 2 tables, users and products. Every product gets created by a user and I need to load the name of the user along with the product info. (So I can display the username and not just the user id.

Any thoughts?

网友答案:

If you have the relations set up correctly:

$this->Product->find(
    'first',
    array(
        'conditions' => array(
            'Product.id' => 2
        ),
        'fields' => array(
            'Product.*',
            'User.name'
        ),
        'recursive' => 1
    )
);
网友答案:

why do you need the subquery?

SELECT Product.*, User.name
FROM products as Product INNER JOIN users as User on (User.id = Product.creator)
WHERE Product.id = 2

anyway to make subqueries read the Complex Find Conditions in the doc

Good Luck

网友答案:

Also, conventions state the foreign key should be user_id (rather than creator) but, if you want to keep that field name, your can specify the correct field in your relations like so:

class Product extends AppModel {
    public $belongsTo = array(
        'User' => array('foreign_key' => 'creator')
    );
}

class User extends AppModel {
    public $hasMany = array(
        'Product' => array('foreign_key' => 'creator')
    );
}
分享给朋友:
您可能感兴趣的文章:
随机阅读: