CakePHP SQL-query count

Question

I have a problem concerning CakePHP SQL-queries. I need to fetch products from the database where shop_id is given and then count the products. All I need is Product.url and its count.

This will do the trick in plain SQL:

SELECT url,COUNT(*) as count FROM products GROUP BY url ORDER BY count DESC;

This one I used to get all products relating to shops:

$this->Product->find('all', array('conditions' => array('Product.shop_id'=>$id)));

That works correctly, but I need to convert that SQL-query above to CakePHP.

I tried something like this:

$this->Product->find('count', array('conditions' => array('Product.shop_id'=>$id),
                                        'fields'=>array('Product.url','Product.id'),
                                        'order'=>'count DESC',
                                        'group'=>'Product.url'));

That returns only an int. But if I run that SLQ-query presented above in mysql server, I get two columns: url and count. How do I get the same results with CakePHP?

It's usually better to leverage "virtual fields" for this: book.cakephp.org/2.0/en/models/virtual-fields.html — mark
– mark, Commented Jul 22, 2013 at 10:37

Patrick · Accepted Answer · 2018-05-04 15:46:12Z

2

You can try this:

$data = $this->Post->query(
    "SELECT COUNT(id),MONTH(created) FROM posts GROUP BY YEAR(created), MONTH(created);"
);

edited May 4, 2018 at 15:46

Patrick

1,7377 gold badges21 silver badges28 bronze badges

answered May 4, 2018 at 15:17

Nguyen Nhi

213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hemppa · Accepted Answer · 2013-07-22 09:04:47Z

1

The most easiest way to do this:

$this->Product->query("SELECT url,COUNT(*) as count FROM products GROUP BY url ORDER BY count DESC;");

...at least for me.

answered Jul 22, 2013 at 9:04

hemppa

2096 silver badges15 bronze badges

Comments

Arun Jain · Accepted Answer · 2013-07-22 09:39:48Z

1

Try the following code:

$this->Product->virtualFields['CNT_PRODUCTS'] = 0;
$this->Product->find('count', array('conditions' => array('Product.shop_id' => $id),
                                    'fields' => array('Product.id', 'Product.url', 'count(*) as CNT_PRODUCTS'),
                                    'order' => array('CNT_PRODUCTS' => 'DESC'),
                                    'group' => 'Product.url'));

answered Jul 22, 2013 at 9:39

Arun Jain

5,4642 gold badges33 silver badges53 bronze badges

1 Comment

Arun Jain Over a year ago

See this link: book.cakephp.org/2.0/en/models/…

Collectives™ on Stack Overflow

CakePHP SQL-query count

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related