PHP Classes

Problem with FindBySql method

Recommend this page to a friend!

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

  1. Problem with FindBySql method   Reply   Report abuse  
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   Report abuse  
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,