CDbCriteria with JOIN and GROUP BY

Yii 1.1 General Discussion
1

I have the following tables in PostgreSQL:

create table shops(

id serial primary key,

name varchar(255),

schema varchar(255),

store_id smallint,

pos_sales_date date,

active boolean

);

create table pos_sales(

shop_id integer NOT NULL,

sales_date date NOT NULL,

sales_year smallint,

sales_month smallint,

sales integer,

price numeric(12,2),

net_price numeric(12,2)

);

alter table pos_sales add constraint pos_sales_pkey PRIMARY KEY(shop_id, sales_date);

alter table pos_sales add constraint fk_PosSales_Shops FOREIGN KEY (shop_id) REFERENCES shops(id);

Now I want to aggregate the table pos_sales on sales_year and sales_month and join the result with shops table. The SQL is:

select shop_id, sum(sales) sales, sum(price) price, sum(net_price) net_price

from pos_sales join shops on shops.id = pos_sales.shop_id

where sales_year=2014 AND sales_month=10 group by shop_id;

I deleted the name field of shops table for simplicity. So my CDbCriteria is as follows:

$criteria=new CDbCriteria;

$criteria->select = ‘t.shop_id as shop_id, sum(t.sales) as sales, sum(t.price) as price, sum(t.net_price) as net_price’;

$criteria->with = array(‘shop’=>array(‘select’=>false, ‘joinType’=>‘INNER JOIN’));

$criteria->condition =‘sales_year=:year AND sales_month=:month’;

$criteria->params = array(’:year’ => $this->year, ‘:month’ => $this->month);

$criteria->group =(‘shop_id’);

$result = PosSales::model()->findAll($criteria);

It forms the following query and complains t the field "t"."sales_date" because it is out of GROUP by clause:

SELECT t.shop_id as shop_id, sum(t.sales) as sales, sum(t.price) as price, sum(t.net_price) as net_price, "t"."sales_date" AS "t0_c1" FROM "pos_sales" "t" INNER JOIN "shops" "shop" ON ("t"."shop_id"="shop"."id")

WHERE (sales_year=:year AND sales_month=:month) GROUP BY shop_id

I don’t know why is adds the field “t”.“sales_date” while I explicitly show fields myself.

When I run this query without join (on table pos_sales only) it runs normally, without adding an extra fields.

Any ideas?

2

Try to execute your both query (with & without join) in your SQL Query window… you will get exact issue.

3

Thank U for reply. When I change "with" part of $criteria tho the following one:

$criteria->join = ‘INNER JOIN shops ON t.shop_id=shops.id’;

I got no error, but it does not add then any shops fields. Look at the changed version:

$criteria=new CDbCriteria;

$criteria->select = ‘t.shop_id as shop_id, shops.name as name, shops.schema as schema, sum(t.sales) as sales, sum(t.price) as price, sum(t.net_price) as net_price’;

$criteria->join = ‘INNER JOIN shops ON t.shop_id=shops.id’;

$criteria->condition =‘t.sales_year=:year AND t.sales_month=:month’;

$criteria->params = array(’:year’ => $this->year, ‘:month’ => $this->month);

$criteria->group =(‘t.shop_id, shops.name, shops.schema’);

$result = PosSales::model()->findAll($criteria);

Although I added name and schema fields of shops table, I don’t get them in result

4

I spat and changed it into native way without model

$result = Yii::app()->db->createCommand()

       ->select("t.shop_id, 


                 s.name as name, 


                 s.schema as schema, 


                 sum(t.sales) as sales, 


                 sum(t.price) as price, 


                 sum(t.net_price) as net_price")


       ->from("pos_sales t")


       ->join("shops s", "t.shop_id=s.id")


       ->where("t.sales_year=:year AND t.sales_month=:month", 


                array(':year' => $this->year, ':month' => $this->month))


       ->group("t.shop_id, s.name, s.schema")


       ->queryAll();
5

I think here is mistakes, not need to use bracket b[/b], check this -


$criteria->group =('t.shop_id, shops.name, shops.schema');

Try this updated code -


$criteria = new CDbCriteria();

$criteria->select = 't.shop_id as shop_id, shops.name as name, shops.schema as schema, sum(t.sales) as sales, sum(t.price) as price, sum(t.net_price) as net_price';

$criteria->join = 'INNER JOIN shops ON t.shop_id=shops.id'; 

$criteria->condition = 't.sales_year=:year AND t.sales_month=:month';

$criteria->params = array(':year' => $this->year, ':month' => $this->month);

$criteria->group = 't.shop_id, shops.name, shops.schema';


$result = PosSales::model()->findAll($criteria);