[sqlalchemy] Re: order_by() and count()
I got it:
>> from sqlalchemy.sql import func
>> stmt = session.query(Prod.store_id,
>> func.count('*').label('prod_count')).group_by(Prod.store_id).subquery()
>> obj_q = session.query(Store, stmt.c.prod_count).outerjoin(
(stmt,
Store.id==stmt.c.store_id)).order_by(stmt.c.prod_count.desc())
Voil`a!
Cheers,
Sandor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] order_by() and count()
hi, i have a typical one to many relationship between two tables: Table_A: contains, for example, "Stores" (id, store_name) Table_B: contains products, "Prod", and which store the products belong to: (id, name, store_id) I need to create a query where i get all "Store" objects ordered by the number of products, e.g: store_3 34 (amount of products) store_1 23 (ditto) store_2 18 (ditto) any idea? cheers, sandor -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Multiple table query ?
Hi Mariano, I tried your code and it worked great. Now I have to look
a way of linking the "union" query with a mapper to obtain a rather
elaborate object than a raw tuple, I mean, when you execute:
q = session.query(Computer).all()
you get Computer objects, but when you execute the "union" query you
get tuples.
When looking at the resulting tuples, I noticed that those tuples that
represent Computer objects contains a field equal to "None" due to the
extra column added in the query. I guess that field could be used
somehow to distinguish between Computer-like tuples and
CompSetComputer-like ones. I'm telling you this b/c I would like to
know if there is a mechanism to link a method to a mapper. This way,
the method would process the "union" query, and would create a list of
Computer and CompSetComputer objects. Do I make myself clear?
Thanks for your help,
Sandor
PS: this is the code I tried. I made some modifications, but it's the
same as yours.
#--
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, select, union_all, schema
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.sql.expression import null
# Engine & Session
engine = create_engine('sqlite:///:memory:', echo=False) # set to True
if you want to activate logging
Session = sessionmaker(bind=engine)
# Current session
session = Session()
# Metadata & Tables & Mappers
metadata = MetaData()
# Tables
comp_table = Table('comp', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('description', String),
Column('ip', String),
# This table has many-to-many relation with the rules table,
# so I don't have a rule_id here, instead I have
# a table rule_computers_table to store rule_ids and computer_ids,
# you know :)
# I included it in my messy-model to specify a relation but it is not
# actually present in the table definition. I appologize.
# That's why I'm including my code, to clarify my situation.
)
compset_comp_table = Table('compset_comp', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('description', String),
Column('ip', String),
Column('compset_id', Integer), # This is actually a ForeinKey to the
Computer_set table
)
# (...)
# Classes
class Computer(object): pass
class CompSetComputer(object): pass
# Mappers
mapper(Computer, comp_table)
mapper(CompSetComputer, compset_comp_table)
# Create the tables
metadata.create_all(engine)
# Testing...
# Insert
c = Computer()
c.name = "comp-1"
c.ip = "1.2.3.4"
c.description = "test comp 1"
session.add(c)
c = Computer()
c.name = "comp-2"
c.ip = "10.20.30.40"
c.description = "test comp 2"
session.add(c)
csc = CompSetComputer()
csc.name = "csc-1"
csc.ip = "11.22.33.44"
csc.description = "test compset-comp 1"
csc.compset_id = "12"
session.add(csc)
csc = CompSetComputer()
csc.name = "csc-2"
csc.ip = "101.202.303.404" # invalid ip address, i know, it's just
for testing :)
csc.description = "test compset-comp 2"
csc.compset_id = "91"
session.add(csc)
session.commit()
# Multi-select
q1 = session.query(Computer.id, Computer.name, Computer.description,
Computer.ip, null())
q2 = session.query(CompSetComputer.id, CompSetComputer.name,
CompSetComputer.description,
CompSetComputer.ip,
CompSetComputer.compset_id)
q = q2.union(q1)
print 80*"-",
print q
print 80*"-"
for x in q:
print x
# Clean up
session.close()
#
--
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Multiple table query ?
Uff, sorry, the word-wrap destroyed my schema :( Sandor -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Multiple table query ?
Hi: I'm working in a pylons application and I have a DB which has the following structure (I haven't depicted the many-to-many tables between, for instance, a computer and a rule for the sake of simplicity): - -- - | computers | | computer_sets | | computer_sets_computers | -- -- id id <--\ id namename \ name description description \ description rule_id ---|rule_id --\\ ip | \ \ ip | \ \ computer_set_id | \ | \ | | rules | \ | \ |-->id <---\ name description enabled There are some points to highlight: 1) A computer_set is only a container of computer_set_computer entities. However, a computer_set_computer is almost identical to a computer entity, with only one difference: a computer entity can be in a rule alone while a computer_set_computer cannot be in a rule; it is considered to be in a rule only as part of a computer_set 2) I have created classes for each of these entities: Computer, ComputerSet, ComputerSet_Computer, Rule However, I have to do the following: I need to be able to query my DB for both Computer and ComputerSet_Computer objects. I mean, I need to display the list of those computers no matter whether or not they belong to a ComputerSet (though when iterating through those objects, I need to know the type of the object). Furthermore, I need to get a Query object to be able to apply slices (to control pagination :) and gain performance. I have tried using UNION and UNION_ALL between the computer and computer_set_computer tables, but I get errors because the tables don't contain the same number of columns. Thanks for your time, Sandor -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
