Query optimization - From 3 queries to 2

[Humberto Moreira]

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!

[Simon Charette]

Hi Humberto,

The following should do:

Provider.objects.prefetch_related(
    Prefetch('service_types', ServiceType.objects.select_related('service_type_category'))
)

P.S. Next time you post a question try to remove data unrelated to your issue from your example (e.g `countries` and `user` reference).

Simon