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
Le dimanche 1 mars 2015 18:33:17 UTC-5, Humberto Moreira a écrit :
>
> 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/e20c1ce6-a60a-4842-a81f-8a3476fcf3a8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
