Getting Different Different DISTINCT result in CakePHP query

Question

I have accepted the Answer. But welcome for more answers and suggestion.

I am getting different - different MySQL result when using distinct in CakePHP query.

What i am trying to do : Getting distinct result of date_generated field.

Problem : Query 1 is working fine but when i am getting two fields (date_generated, id ) (see query 2) then distinct result not working.

Query 1

$this->Purchase->find('all',array( 
                               'order' => array('Purchase.date_generated ASC'),
                               'fields' => array('DISTINCT Purchase.date_generated')));

Result with Query 1 : in this result date_generated is not repeating and Working fine. Please check Query 2.

Array
(
    [0] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 1970-01-01
                )

        )

    [1] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-17
                )

        )

    [2] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-18
                )

        )

    [3] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-19
                )

        )

    [4] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-22
                )

        )

    [5] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-29
                )

        )

    [6] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-08-01
                )

        )

)

Query 2 : only added 'Purchase.id'

$this->Purchase->find('all',array( 
                               'order' => array('Purchase.date_generated ASC'),
                               'fields' => array('DISTINCT Purchase.date_generated','Purchase.id')));

Result of Query 2 : In this result Array index 5,6,7 date_generated repeating.

Array
(
    [0] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 1970-01-01
                    [id] => 9
                )

        )

    [1] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-17
                    [id] => 1
                )

        )

    [2] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-18
                    [id] => 2
                )

        )

    [3] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-19
                    [id] => 3
                )

        )

    [4] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-22
                    [id] => 4
                )

        )

    [5] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-29
                    [id] => 5
                )

        )

    [6] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-29
                    [id] => 6
                )

        )

    [7] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-06-29
                    [id] => 7
                )

        )

    [8] => Array
        (
            [Purchase] => Array
                (
                    [date_generated] => 2014-08-01
                    [id] => 8
                )

        )

)

Petr Chudanic · Accepted Answer · 2014-08-21 08:18:49Z

3

This seems like standard sql behaviour. First query produces something like this

select DISTINCT Purchase.date_generated from Purchase order by Purchase.date_generated ASC

the second one produces query like that

select DISTINCT Purchase.date_generated, Purchase.id from Purchase order by Purchase.date_generated ASC

The results of those queries are exactly what you got. For the socond one it will return distinct pairs of Purchase.date_generated and Purchase.id. So if for some date there is several purchase ids each of them is distinct pair.

So if you are after distinct date only you should use the first query.

answered Aug 21, 2014 at 8:18

Petr Chudanic

5474 silver badges12 bronze badges

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

3 Comments

Naresh Over a year ago

ok thankx . But when i need both date_generated and id then what should i do ?

Petr Chudanic Over a year ago

It depends on how you would like your output to look like. Could you put sample expected output in your question?

Naresh Over a year ago

i think its fine i will use only first query. thankx.

Collectives™ on Stack Overflow

Getting Different Different DISTINCT result in CakePHP query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related