I'm experiencing a problem with MyActiveRecord 0.3.
I have the following table:
Now, I tried to find out the number of companies having the same objective. In SQL I would write:
SELECT objective, count(company_id) FORM objectives WHERE 1 GROUP BY company_id
Trying this query directly on the DB it works pretty well, but using MAR 0.3 in the following way:
$result = MyActiveRecord::FindBySQL('strategies','SELECT objective, count(company_id) as num FROM strategies GROUP BY objective ORDER BY num DESC');
I obtain only the last record, while I can obtain the correct result including the id of the table in the query:
$result = MyActiveRecord::FindBySQL('strategies','SELECT id, objective, count(company_id) as num FROM strategies GROUP BY objective ORDER BY num DESC');
So, it seems to me that I should always include the id into the query, is that correct?
I read through the documentation provided (API, howto and background) but I probably missed something.
Thank you for your attention and, of course, for this great piece of software you provided.
Jake Grimley - 2006-11-19 16:16:47 - In reply to message 1 from Libra Veroen
In general, MyActiveRecord is SQL/Object mapper. So you can always expect strange results when using aggregate SQL functions which don't map neatly to objects.
I believe you might use the FreqDist method to get what you are asking for though:
foreach( MyActiveRecord::FreqDist('Objective', 'objective') as $objective=>$total )
print "There are $total companies with the objective: $objective";
(I'm not sure if I've understood exactly what you're trying to do, but I think this is close).
The short answer to your question though, is that you do need the id because when MyActiveRecord returns an array of object, the array is keyed by the id of the object. Although this is documented, a lot of people don't notice. This can be handy because if you have an array of objects you can refer to an object directly by it's id.
So when you are running the query with no id, the array will have used the same (blank) key for every object, hence you only found the last object in your array.