Query to find number of linked foreign key objects?

[James Masters]

Is it possible to construct a query that will find any records that
have at least 5 objects linked to it via a specific foreign key? e.g.
in an address database, I might have a person table with a foreign key
to a phone table. So multiple phone records link to a person record.
Using only a Rose query, can I obtain all persons with 3 or more phone
records?

many thanks for any advice.

[Perrin Harkins]

On Tue, Feb 28, 2012 at 2:04 AM, James Masters <ja...@mastersgames.com> wrote:
> Is it possible to construct a query that will find any records that
> have at least 5 objects linked to it via a specific foreign key?

Sure, but you'll either need to use GROUP BY...HAVING or a derived
table (subquery). Try get_objects_from_sql().

- Perrin

[jfrm]

This is helpful thank you.  Now I have managed to construct an SQL query that works e.g.:

SELECT ... FROM persons,phones WHERE persons.uid = phones.personuid GROUP BY person.uid HAVING count(person.uid) > 5; 

But I can't see how to convert this to a Rose::HTML query. I've got as far as:

get_persons(query => [], with_objects => ['phones'], group_by => 'person.uid')

but I can't see HAVING as an option in the Rose query documentation nor is it clear to me how I can incorporate the SQL function COUNT into the query.  Is this possible?

Or should I go to plan B and just use direct SQL in the rose query?  Or is this hopeless and I should go to plan C (presumably less efficient) and grab all the customers and then use Perl to narrow them down?

[jfrm]

Now I've found the answer. For posterity, as the manual clearly says the GROUP BY method should be 'fully formed SQL'. So the following GROUP BY argument worked: 't1.uid having count(t1.uid) > 5'

I am left with a niggling problem though.  In the subsequent report, one of the columns shown is 'Number of phone numbers' for which I was using @{$person->phones}.  This now fails and gives a count of 1 - presumably only one row is returned by rose so only 1 order is thought to exist.  Before I used GROUP BY, it worked fine. In SQL if I use COUNT('persons.uid'), the correct value is shown.  So unfortunately, I am back to doing the thing in PERL unless anyone knows a way to fix this problem?

 

[John Siracusa]

On Mon, Mar 5, 2012 at 4:54 AM, jfrm <ja...@mastersgames.com> wrote:
>> Now I've found the answer. For posterity, as the manual clearly says the
>> GROUP BY method should be 'fully formed SQL'. So the following GROUP BY
>> argument worked: 't1.uid having count(t1.uid) > 5'

You shouldn't try to use "group by" with the Manager's get_objects()
method (or anything derived from it, like get_persons()). It expects
to create a tree of RDBO objects from the results, one object for each
uniquely identified row in a table. Once you use "group by," you're
definitely not going to get result rows like that. I suggest making a
Manager method that uses get_objects_sql() to build the WHERE part of
the query, builds the rest of the query "manually," runs it, then
builds and returns whatever result objects or data structures are
appropriate.

-John