Here is full statement *generated by cake*. It raises sql-error:
SELECT `TestProduct`.`title`, `TestType`.`description`,
`TestProductItem`.`size`, COUNT(*) FROM `test_product_items` AS
`TestProductItem` LEFT JOIN `test_products` AS `TestProduct` ON
(`TestProductItem`.`test_product_id` = `TestProduct`.`id`) WHERE 1 = 1
GROUP BY `TestProduct`.`id`, `TestProductItem`.`size` ORDER BY
`TestProductItem`.`id` asc LIMIT 25
Error:
SQL Error: 1054: Unknown column 'TestType.description' in 'field
list' [CORE/cake/libs/model/datasources/dbo_source.php, line 666]
This is *expected* to be generated by cake, but is not:
SELECT `TestProduct`.`title`, `TestType`.`description`,
`TestProductItem`.`size`, COUNT(*) FROM `test_product_items` AS
`TestProductItem` LEFT JOIN `test_products` AS `TestProduct` ON
(`TestProductItem`.`test_product_id` = `TestProduct`.`id`) LEFT JOIN
`test_types` AS `TestType` ON (`TestProduct`.`test_type_id` =
`TestType`.`id`) WHERE 1 = 1 GROUP BY `TestProduct`.`id`,
`TestProductItem`.`size` ORDER BY `TestProductItem`.`id` asc LIMIT 25
As you can see in first sql query, only join statement for
test_products table is generated, but no for test_types.
A. Below are source files:
=== controllers/test_product_items_controller.php ===
class TestProductItemsController extends AppController {
var $paginate = array(
'limit' => 25,
'fields' => 'TestProduct.title, TestType.description,
TestProductItem.size, COUNT(*)',
'order' => array(
'TestProductItem.id' => 'asc'
),
'group' => array(
'TestProduct.id, TestProductItem.size'
)
);
function index() {
$this->set('testProductItems',
$this->paginate('TestProductItem'));
}
}
=== models/test_product.php ===
class TestProduct extends AppModel {
var $belongsTo = array('TestType');
var $hasMany = array('TestProductItem');
}
=== models/test_type.php ===
class TestType extends AppModel {
var $hasMany = array('TestProduct');
}
=== models/test_product_item.php ===
class TestProductItem extends AppModel {
var $belongsTo = array('TestProduct');
}
B. Database schema & data:
CREATE TABLE `test_products` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(128) NOT NULL,
`test_type_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
CREATE TABLE `test_product_items` (
`id` int(11) NOT NULL auto_increment,
`test_product_id` int(11) NOT NULL,
`size` varchar(16) NOT NULL,
`sold_time` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
CREATE TABLE `test_types` (
`id` int(11) NOT NULL auto_increment,
`description` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO `test_products` VALUES (1, 'Star Jeans 502', 4);
INSERT INTO `test_product_items` VALUES (7, 1, 'S', NULL);
INSERT INTO `test_product_items` VALUES (8, 1, 'S', NULL);
INSERT INTO `test_product_items` VALUES (9, 1, 'S', NULL);
INSERT INTO `test_product_items` VALUES (10, 1, 'M', NULL);
INSERT INTO `test_product_items` VALUES (11, 1, 'M', NULL);
INSERT INTO `test_product_items` VALUES (12, 1, 'L', NULL);
INSERT INTO `test_types` VALUES (4, 'Trousers');
Thanks in advance,
T.
On 27 Maj, 05:38, Jeremy Burns <jeremybu...@me.com> wrote:
> All of your table and field names should be lower case.
>
> What is your full find statement? Can you post it all, not just a small piece.
>
> Jeremy Burns
> jeremybu...@me.com
>
> On 27 May 2010, at 00:22, 朝の木 wrote:
>
> > I have three models (with controllers):
> > - Product: Id, Title, Type_id
> > - Type: Id, Description
> > - ProductItem: Id, Product_id, Size, Sold_time
>
> > Relationships:
> > - Product: belongsTo(Type), hasMany(ProductItem)
> > - Type: hasMany(Product)
> > - ProductItem: belongsTo(Product)
>
> > Example:
> > - Product:
> > 1; 'Star Jeans 502', 4
> > (...)
>
> > - Type:
> > 4; 'Trousers'
> > (...)
>
> > - ProductItem:
> > 7; 1; 'S', NULL
> > 8; 1; 'S', NULL
> > 9; 1; 'S', NULL
> > 10; 1; 'M', NULL
> > 11; 1; 'M', NULL
> > 12; 1; 'L', NULL
> > (...)
>
> > Now, I'd like to fetch the in such way:
> > Description, Type, Size, Count
> > Star Jeans 502, 'Trousers', 'S', 3
> > Star Jeans 502, 'Trousers', 'M', 2
> > Star Jeans 502, 'Trousers', 'L', 1
>
> > The sql query should look like this:
> > select Product.Title, Type.Description, ProductItem.Size,
> > COUNT(ProductItem.*)
> > left join Type on Product.Type_id = Type.id left join ProductItem on
> > ProductItem.Product_id = Product.id
> > group by Product.Id, ProductItem.Size
>
> > Is it correct?
>
> > I want to make such sql in cakephp in automagicalled way using find in
> > ProductItem controller. Unfortunately it does not work. I tried find
> > with parameter: field => (Product.Title, Type.Description,
> > ProductItem.Size, COUNT(ProductItem.*)) and group => (Product.Id,
> > ProductItem.Size), but cake doesn't want to put join on ProductType.
>
> > I think that cake builds joins only for tables in relations with
> > current model (ProductItem), and does not look deeper -
> > "belongsTo(Type)" is defined in Product model.
>
> > How to solve this problem?
>
> > Kind regards,
> > T.
>
> > Check out the new CakePHP Questions sitehttp://cakeqs.organd help others
> > with their CakePHP related questions.
>
> > You received this message because you are subscribed to the Google Groups
> > "CakePHP" group.
> > To post to this group, send email to cake-php@googlegroups.com
> > To unsubscribe from this group, send email to
> > cake-php+unsubscr...@googlegroups.com For more options, visit this group
> > athttp://groups.google.com/group/cake-php?hl=en
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
