Query optimization - From 3 queries to 2

I basically have to display a list of service providers and in each, I need to display the categories of service they offer.

So as an example:

Possible Service Type Categories:

[id: 1, name:'Programming']  [id: 2, name:'Design']

Possible Service Types:

[id: 1, name: 'PHP Service', service_type_category_id: 1]  [id: 2, name: 'JAVA Service', service_type_category_id: 1]  [id: 3, name: 'Web Design Service', service_type_category_id: 2]

Example of Display Results:

Company Blue offers 'Programming'  Company Test offers 'Programming' and 'Design'  Company Orange offers 'Design' ....

I'm trying to write the least number of queries:

I have these models:

class ServiceTypeCategory( BaseModel ):        # Model Attributes      name = models.CharField( _( "name" ), max_length = 40 )      class ServiceType( BaseModel ):        # Model Attributes      service_type_category = models.ForeignKey( 'ServiceTypeCategory', verbose_name = _( 'category' ) )      name = models.CharField( _( "name" ), max_length = 60 )      description = models.TextField( _( "description" ) )      class Provider( BaseModel ):        # Model Attributes      display_name = models.CharField( _( "name" ), max_length = 80 )        # Many to many relations      countries = models.ManyToManyField( 'core.Country' ) # countries this provider support      service_types = models.ManyToManyField( 'ServiceType', through = 'Provider_ServiceTypes', related_name = 'service_types' )      class Provider_ServiceTypes( BaseModel ):        # Model Attributes      service_type = models.ForeignKey( 'ServiceType', verbose_name = _( 'service type' ) )      provider = models.ForeignKey( 'Provider', verbose_name = _( 'provider' ) )      is_top = models.BooleanField( _( "is top service" ), default = False )

Then, to run the query, I have the following:

providers = Provider.objects.select_related(      'user',  ).prefetch_related(      Prefetch(          'service_types__service_type_category',          queryset = ServiceTypeCategory.objects          .only( 'name' )      )  ).filter(      countries = country_id,  ).only(      'id', 'display_name', 'user'  ).order_by(      '-user__last_login'  )

This works out well, but it runs the 3 following queries:

SELECT app_provider.id, app_provider.user_id, app_provider.display_name, core_user.id, core_user.password, core_user.last_login, core_user.is_superuser, core_user.created_date, core_user.modified_date, core_user.email, core_user.name, core_user.is_active, core_user.is_admin   FROM app_provider   INNER JOIN app_provider_countries ON ( app_provider.id = app_provider_countries.provider_id )   INNER JOIN core_user ON ( app_provider.user_id = core_user.id )   LEFT OUTER JOIN core_userpersonal ON ( core_user.id = core_userpersonal.user_id )   LEFT OUTER JOIN core_userstats ON ( core_user.id = core_userstats.user_id )   WHERE app_provider_countries.country_id = 204   ORDER BY core_userstats.total_reviews DESC, core_userstats.total_contracts DESC, core_userstats.total_answers DESC, core_user.last_login DESC LIMIT 5      SELECT (app_provider_servicetypes.provider_id) AS _prefetch_related_val_provider_id, app_servicetype.id, app_servicetype.created_date, app_servicetype.modified_date, app_servicetype.service_type_category_id, app_servicetype.name, app_servicetype.description   FROM app_servicetype   INNER JOIN app_provider_servicetypes ON ( app_servicetype.id = app_provider_servicetypes.service_type_id )   WHERE app_provider_servicetypes.provider_id IN (2)      SELECT app_servicetypecategory.id, app_servicetypecategory.name   FROM app_servicetypecategory   WHERE app_servicetypecategory.id IN (1, 2)

Question is: How can I make to run just 2 queries in total? (The last 2 queries should be joined with INNER JOIN and a group by per service_type_category_name)

Thanks in advance!

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/3daddb38-3260-4f7d-9559-7d0d3f17b59e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.