Login   Register  
PHP Classes
elePHPant
Icontem

Problem with FindBySql method

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us

      My Active Record  >  All threads  >  Problem with FindBySql method  >  (Un) Subscribe thread alerts  
Subject:Problem with FindBySql method
Summary:Unexpected behaviour of a query
Messages:2
Author:Libra Veroen
Date:2006-11-19 11:37:23
Update:2006-11-19 16:16:47
 

  1. Problem with FindBySql method   Reply  
Picture of Libra Veroen
Libra Veroen
2006-11-19 11:37:23
I'm experiencing a problem with MyActiveRecord 0.3.
I have the following table:
TABLE objectives
{
id integer
company_id integer
objective text
}

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.
Best regards
Libra Veroen


  2. Re: Problem with FindBySql method   Reply  
Picture of Jake Grimley
Jake Grimley
2006-11-19 16:16:47 - In reply to message 1 from Libra Veroen
Hi Libra,

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.

Hope this helps,
Jake