AR and this query

Yii 1.1 General Discussion
1

Ok, I’m trying to use AR for this query, but I don’t have clue how to do that :)

this is sql:


SELECT SUM(count) FROM `order_details` WHERE `idorders` IN

(SELECT `idorders` FROM `orders` WHERE `customer_company`=1)

In general, I have two tables, orders and order_details and now I want to get total count of ordered toners for selected company. I’m not interested in count for offices, just companies.

This is schema for orders table:


CREATE TABLE IF NOT EXISTS `orders` (

  `idorders` int(11) NOT NULL AUTO_INCREMENT,

  `date` date DEFAULT NULL,

  `customer_name` varchar(100) DEFAULT NULL,

  `customer_email` varchar(100) NOT NULL,

  `customer_company` int(11) DEFAULT NULL,

  `company_office` int(11) DEFAULT NULL,

  `open` tinyint(1) NOT NULL DEFAULT '1',

  PRIMARY KEY (`idorders`),

  KEY `fk_orders_companies1` (`customer_company`),

  KEY `fk_orders_offices1` (`company_office`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=327 ;

And this one is for order_details table:


CREATE TABLE IF NOT EXISTS `order_details` (

  `idorder_details` int(11) NOT NULL AUTO_INCREMENT,

  `idorders` int(11) DEFAULT NULL,

  `idtoners` int(11) DEFAULT NULL,

  `count` int(11) DEFAULT NULL,

  PRIMARY KEY (`idorder_details`),

  KEY `fk_order_details_orders1` (`idorders`),

  KEY `fk_order_details_toners1` (`idtoners`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=379 ;

Is something like this even possible with AR model, and if it is, how??? :)

Thanks in advance.

p.s. query that I use in example works as expected.

2

If the following query is returning the same result as your query than yes, it is possible with AR:


SELECT SUM(`count`)

FROM `order_details`

INNER JOIN `orders` USING(`idorders`)

WHERE `customer_company`=1
3

You can achive with a bit of conditions:




$criteria= new CDbCriteria;

$criteria->select= ' SUM(`count`) as sum';

$criteria->join= 'INNER JOIN `orders` USING(`idorders`)';

$criteria->condition='`customer_company`=1';

orderDetail::model()->findAll($criteria);

In orderDetail you have also to add a property sum, in wich you will get the result.

4

Than you very much, both of you. Works as expected :)