Tomas, apologies for the original reply. My SQL skills are quite weak and doing the query wit joins did not occur to me. Is using the joins more efficient? I can't see a difference in the result returned when I run either of these queries.
Thanks CC On Mon, Apr 12, 2010 at 4:21 PM, Tomas Lestach <tlest...@redhat.com> wrote: > Hey Colin, > > why don't you just list all the data you need in the SELECT section and the > tables that are necessary for the select in the FROM section? > > What I mean is to write: > > SELECT DISTINCT C.id, > C.name, > C.parent_channel AS parent_id, > C.label AS channel_label, > (SELECT COUNT(P.package_id) > FROM rhnChannelPackage P > WHERE P.channel_id = C.id > ) AS package_count, > CA.name AS arch_name > FROM rhnChannel C > inner join rhnChannelArch CA ON CA.ID = C.channel_arch_id > inner join rhnUserChannel UC ON UC.channel_id = C.id > left join rhnChannel C2 ON C2.parent_channel = C.id > WHERE UC.user_id = :user_id AND > (C.org_id = :org_id OR ( C2.id IS NOT NULL AND C2.org_id = :org_id > )) > > > instead of: > > select Distinct C.id, > C.name, > C.parent_channel as parent_id, > C.label as channel_label, > (SELECT COUNT(P.package_id) > FROM rhnChannelPackage P > WHERE P.channel_id = C.id > ) AS package_count, > (select CA.name from rhnChannelArch CA where CA.ID = > C.channel_arch_id) arch_name > from rhnChannel C inner join > rhnUserChannel UC on UC.channel_id = C.id left join > rhnChannel C2 on C2.parent_channel = C.id > where UC.user_id = :user_id AND > (C.org_id = :org_id or ( C2.id is not null AND C2.org_id = > :org_id )) > > > > Regards, > Tomas > > -- > Tomas Lestach > RHN Satellite Engineering, Red Hat > > ----- "Colin Coe" <colin....@gmail.com> wrote: > >> OK, I think this patch is OK now. >> >> CC >> >> On Thu, Apr 8, 2010 at 9:10 PM, Colin Coe <colin....@gmail.com> >> wrote: >> > Hi Justin >> > >> > Is this a bit better? http://fpaste.org/LJwb/ >> > >> > Thanks >> > >> > CC >> > >> > >> > >> > On Thu, Apr 8, 2010 at 5:49 AM, Colin Coe <colin....@gmail.com> >> wrote: >> >> Hi Justin >> >> >> >> Thanks for the feedback. I'll have another look at this and >> re-submit. >> >> >> >> CC >> >> >> >> On Thu, Apr 8, 2010 at 1:21 AM, Justin Sherrill >> <jsher...@redhat.com> wrote: >> >>> On 4/6/10 9:55 AM, Colin Coe wrote: >> >>>> Hi all >> >>>> >> >>>> As per the API Addition page, I've added 'arch' to the >> >>>> channel.list*Channels API calls. >> >>>> >> >>>> Comments/criticisms welcome >> >>>> >> >>>> CC >> >>>> >> >>>> >> >>>> >> >>>> >> >>>> _______________________________________________ >> >>>> Spacewalk-devel mailing list >> >>>> Spacewalk-devel@redhat.com >> >>>> https://www.redhat.com/mailman/listinfo/spacewalk-devel >> >>> Hey Colin, >> >>> >> >>> Having it do the lookups of package Arch in the Serializer really >> isn't >> >>> the right way to do it as Serializers are just meant to translate >> the >> >>> existing data. >> >>> >> >>> I would either add joins to rhnChannelArch to get the label in >> each of >> >>> the queries or write a elaborator that just gets the channel arch >> and >> >>> make each of those queries use the elaborator. >> >>> >> >>> Thanks, >> >>> >> >>> -Justin >> >>> >> >>> >> >>> -- >> >>> Justin Sherrill, RHCA 1801 Varsity Drive. >> >>> Software Engineer Raleigh, NC 27603 >> >>> Red Hat, Inc. >> >>> >> >>> _______________________________________________ >> >>> Spacewalk-devel mailing list >> >>> Spacewalk-devel@redhat.com >> >>> https://www.redhat.com/mailman/listinfo/spacewalk-devel >> >>> >> >> >> > >> > >> > >> > -- >> > RHCE#805007969328369 >> > >> >> >> >> -- >> RHCE#805007969328369 >> >> _______________________________________________ >> Spacewalk-devel mailing list >> Spacewalk-devel@redhat.com >> https://www.redhat.com/mailman/listinfo/spacewalk-devel > > _______________________________________________ > Spacewalk-devel mailing list > Spacewalk-devel@redhat.com > https://www.redhat.com/mailman/listinfo/spacewalk-devel -- RHCE#805007969328369 _______________________________________________ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
