CakePHP database queries DISTINCT / GROUP BY error

Question

In my CakePHP model I'm trying to get some data from my table.
I tried using DISTINCT but it seems like using DISTINCT doesn't change the query results.
I can see many rows that has the same nick

with 'DISTINCT Mytable.nick'

$this->Mytable->find('all',
    array(
        'fields'=> array(
            'DISTINCT Mytable.nick',
            'Mytable.age', 'Mytable.location',
        ),
        'conditions' => array('Mytable.id >=' => 1, 'Mytable.id <=' => 100),
        'order' => array('Mytable.id DESC')
));

with 'group Mytable.nick'

$this->Mytable->find('all',
    array(
        'fields'=> array(
            'Mytable.nick',
            'Mytable.age', 'Mytable.location',
        ),
        'conditions' => array('Mytable.id >=' => 1, 'Mytable.id <=' => 100),
        'group' => 'Mytable.nick',
        'order' => array('Mytable.id DESC')
));

with 'Mytable.nick'

$this->Mytable->find('all',
    array(
    'fields'=> array(
            'Mytable.nick',
            'Mytable.age', 'Mytable.location',
    ),
    'conditions' => array('Mytable.id >=' => 1, 'Mytable.id <=' => 100),
    'order' => array('Mytable.id DESC')
));

Edit: It seems like even CakePHP 2.1 can't use DISTINCT option. When I tried "GROUP BY" it solved my issue. But as you can see from my query I need to order results with Mytable.id descended. When I use GROUP BY, when Mysql finds relevant row, it doesn't take others. For example.

id=1, nick=mike, age=38, location=uk
id=2, nick=albert, age=60, location=usa
id=3, nick=ash, age=42, location=uk
id=4, nick=albert, age=60, location=new_zelland

When I use group Mytable.nick, I don't see 4th row in my results, I see 2nd row. Because when mysql saw "albert" second time, it doesn't put it into my results. But I need latest "albert" result. Is it not possible?

Edit2: It seems like order by/group by conflict is a common problem. I found some tips in this question. But it gives solution for native Mysql queries. I need a solution for CakePHP type queries.

Think this is answered here: http://stackoverflow.com/questions/1718482/using-distinct-in-a-cakephp-find-function — ficuscr, Aug 27 '12 at 20:33
Sounds like you need to GROUP BY each field returned to prevent the grouping on 'nick' alone. — ficuscr, Aug 27 '12 at 21:02
I think the link you provide shows the added complexity when returning a limited offset used with order/group by. I do not think you face the same obstacle. What you are trying to do should certainly be possible even with Cake. That said when I had to use that "framework" ended up with more that a few `$db->rawQuery()`. — ficuscr, Aug 27 '12 at 21:18

ficuscr · Answer 1 · 2012-08-27T22:20:49.520

Not clear on why you want to group by nick and order by id. Do you intend to use an aggregate function like COUNT() to see how many occurrences of the same nick there are? In short you overall goal still is not clear to me. Might be worth being aware of the HAVING MySQL keyword.

Updated: Ok, that makes more sense. So you need to use a sub select on the condition or perhaps express that as a join. I'll try and show an example using the sub select in the WHERE clause.

/* select last occurrence for each nick (if you need one for each location )*/
SELECT nick, age, location
FROM myTable t1
WHERE id =
    (SELECT MAX(id) 
    FROM myTable t1
    WHERE t1.nick = t2.nick);

Would think something like this would work:

$this->Mytable->find('all',
    array(
    'fields'=> array(
            'Mytable.nick',
            'Mytable.age', 'Mytable.location',
    ),
    'conditions' => array('Mytable.id =' => '(SELECT MAX(id) FROM myTable t2 WHERE myTable.nick = t2.nick)', 'Mytable.id <=' => 100)
));

changing my group line to **'group' => array('Mytable.nick', 'Mytable.age', 'Mytable.location')** and **removing group line** gives same results. — trante, Aug 27 '12 at 21:51
this is a login table. I record each login of user to this table. Sometimes I need to query users. I want to know latest locations, ages, etc. of user. Users can have different loactions in each login. So I need latest record of each user. — trante, Aug 27 '12 at 21:56

CakePHP database queries DISTINCT / GROUP BY error

1 Answers1