Hello, I have a rather complicated query on a medium sized database (millions of rows). One part of the query looks like the following :
SELECT COUNT(sp.id), COUNT( (SELECT sp.id WHERE sp.site_id IN ( SELECT si.id FROM sites si WHERE si.latitude IS NOT NULL AND si.longitude IS NOT NULL ) ) ) AS foo FROM specimens sp WHERE sp.determinator_id = 4; The problem I have is that SQLAlchemy always generate a FROM statement for a select(), even with from_obj=[] or from_obj=None, so in my case for the second count() it generates : count( ( SELECT sp.id FROM specimens AS sp WHERE sp.site_id IN ( SELECT sites.id FROM sites WHERE sites.latitude IS NOT NULL AND sites.longitude IS NOT NULL ) ) ) AS specimen_filtered_georeferenced The problem is that in my case I don't want the "FROM specimens AS sp" statement. Is there a way to avoid that ? I have the following code for the moment: (...) filters = validation.SearchFilter.to_python(request.params) search = SpecimenSearch(filters) sp = model.t_specimens.alias('sp') specimen_where_clause = search.specimen_filters(sp) specimen_from_clause = sp specimen_fields = [sp.c.taxonomy_id, func.count(sp.c.id).label('specimen_filtered'), ##### Problem is here ##### func.count( select( [sp.c.id], sp.c.site_id.in_( select( [model.t_sites.c.id], and_( model.t_sites.c.latitude != None, model.t_sites.c.longitude != None, ) ) ) ) ).label('specimen_filtered_georeferenced'), ############################## func.count(sp.c.type_id).label('count_type'), select( [func.count(model.t_specimens.c.id)], model.t_specimens.c.taxonomy_id == sp.c.taxonomy_id ).label('specimen_total_taxonomy')] (...) q_specimens = select(specimen_fields, and_(*specimen_where_clause), from_obj = [specimen_form_clause], group_by = sp.c.taxonomy_id ).alias('specimen') (...) from_clause_taxonomy = model.t_taxonomies.outerjoin(model.t_families).\ outerjoin(model.t_genuses).outerjoin(model.t_species).\ outerjoin(model.t_subspecies) q_taxonomy = select( [q_specimens, model.t_taxonomies.c.id, model.t_families, model.t_genuses, model.t_species, model.t_subspecies], and_(*search.taxonomy_filters()), from_obj = [from_clause_taxonomy.join(q_specimens)] ).order_by(model.t_families.c.name, model.t_genuses.c.name, model.t_species.c.name, model.t_subspecies.c.name ).apply_labels() (...) Thanks, Julien -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles Mail: [EMAIL PROTECTED] @biobel: http://biobel.biodiversity.be/person/show/471 Tel : 02 650 57 52 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---