Criteria get items with max attribute

Yii 1.1 General Discussion
1

Hello, I am trying to do something in my search method that gets all items in table X, groups them by Name, and grabs the rows for each Name with the highest attribute Y.

For example, if I have a table with 2 columns, name and type:

  • Apple 1

  • Orange 1

  • Orange 2

  • Banana 5

  • Banana 6

  • Banana 7

I would want to grab all of the like items with the highest type #. So in this case, it would return:

  • Apple 1

  • Orange 2

  • Banana 7

I was trying to do this with $criteria->group, $criteria->having and so on and so forth, but I was having some trouble. Any ideas? Thanks.

2

Just a suggestion:

First of all you need to come out with the SQL query that gives you the result you need… only then you can try to turn that SQL into a criteria…

So if you have two columns… name and number

you are looking for something like


select * group by name order by name, number desc

NOTE: I’m a bit in a hurry now… so not tested… just my thinking on this topic :)… hope it helps you

3 



SELECT name, type

FROM Engines AS e1 

WHERE 

    (SELECT COUNT(*) 

                FROM Engines AS e2 

                WHERE e2.name = e1.name AND e2.type > e1.type

    ) = 0

just not sure how to combine all these different types of statements

4

Interesting problem.

Check if this will work




SELECT DISTINCT * 

  FROM Engines e1 

  WHERE e1.type=(

    SELECT MAX(e2.type) 

      FROM Engines e2 

      WHERE e1.name=e2.name

  )

  ORDER BY e1.name







$criteria = new CDbCriteria;

$criteria->alias = 'e1';

$criteria->distinct = true;

$criteria->condition = 'e1.type=(SELECT MAX(e2.type) FROM Engines e2 WHERE e1.name=e2.name)';

$criteria->order = 'e1.name';

/Tommy

5

@rymonator

Why are you using double select? and why count()…

If you have already the numbers and just need the biggest then you can use this SQL:


SELECT company_id,max(category_id) FROM companycategory group by company_id
6

This does not give the correct result with the test data I use (doesn’t find the highest value for type). I guess order by should have to be applied before group by, but that’s not valid (according to mySQL/phpMyAdmin).

/Tommy

7

Tommy, do you use MySQL? What mdomba described is the expected behavior for MAX() if you use aggregation (GROUP BY):

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

8

Now I tried this with rymonator’s example data (x 3) and it works just fine. Must be something with the table I used for testing. (One of my existing tables, the column I decided to use as type happens to be PK.) So I overlooked the easy solution mdomba suggested.

Edit: My mistake, I jumped the test in phpMyAdmin, went directly to Yii coding and forgot to add an alias to the max(type) clause, for replacing the raw attribute value in my existing gridview.

/Tommy

9

Sorry, the example I gave was not sufficient. This works in that particular case, but if I want to get ALL of the attributes from a row, it actually mixes up the data. For example, if I use that statement you posted on this dataset:

name – desc – development – eot – build

apple – a – b – 333 – 5

apple – x – y – 999 – 4

banana – car – plane – 0 – 2

It will give me something like:

apple – x – y – 999 – 5

banana – car – plane – 0 – 2

I think it depends on the order of the data in the table, but over a lot of rows, it certainly won’t be accurate. Sorry for giving a poor example. The reason I use that SQL statement was because it caters towards the entire row’s attributes. Thanks.

10

I did not understand your dataset…

but if you want all the fields from a row you can use the * like


SELECT *,max(category_id) as max FROM companycategory group by company_id
11

Thanks, that worked! It messed up my filter but I’ll see if I can fix that.

12

If you try that on a dataset with several columns it will mix up the data. For example, the name and the build will be correct, but the description may be from a different row with the same name.