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+unsubscr...@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.
