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
-~----------~----~----~----~------~----~------~--~---
