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?
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().
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?
> 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?
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.
