Hi,
I have the following scenario:
- an "office" can have multiple "computer". (one-to-many)
- an "office" can have multiple "netconfig" (one-to-many). This is a
tuple of (ip, mask, gateway, dns).
- a "computer" can have one or none "netconfig" (one-to-one).
### OFFICES
office_table = Table('offices', meta,
Column('id', Integer, primary_key=True),
Column('name', Unicode(40), nullable=False, unique=True),
)
class Office(object):
pass
office_mapper = assign_mapper(ctx, Office, office_table,
properties={
'computers':relation(Computer, backref="office"),
'netconfigs':relation(Netconfig, backref="office")
}
)
### COMPUTERS
computer_table = Table('computers', meta,
Column('id', Integer, primary_key=True),
Column('id_netconfig', None, ForeignKey('netconfigs.id')),
Column('id_office', None, ForeignKey('offices.id'), nullable=False),
)
class Computer(object):
pass
computer_mapper = assign_mapper(ctx, Computer, computer_table,
properties={
'netconfig':relation(Netconfig, backref=backref("computer"))
}
)
### NETCONFIGS
netconfig_table = Table('netconfigs', meta,
Column('id', Integer, primary_key=True),
Column('ip', Unicode(15)),
Column('mask', Unicode(15)),
Column('gateway', Unicode(15)),
Column('dns', Unicode(15)),
Column('id_office', None, ForeignKey('offices.id')),
)
class Netconfig(object):
pass
netconfig_mapper = assign_mapper(ctx, Netconfig, netconfig_table)
office.netconfigs: returns a list of netconfigs
office.computers: returns a list of computers
computer.netconfig: returns a scalar netconfig or None
netconfig.computer: returns a scalar computer or None
My problem: I'm trying to select a list of available netconfigs of an
office, the ones not already taken by a computer of that same office.
But I'm having a hardtime getting around building up the query:
computer = Computer.get(id)
netconfig_list =
Netconfig.query().filter(Netconfig.c.id_office==computer.id_office).join('computers').filter(Computer.c.id_netconfig==None).select()
But this returns an empty list.
SELECT netconfigs.id_office AS netconfigs_id_office, netconfigs.name AS
netconfigs_name, netconfigs.ip AS netconfigs_ip, netconfigs.dns AS
netconfigs_dns, netconfigs.mask AS netconfigs_mask, netconfigs.gateway
AS netconfigs_gateway, netconfigs.id AS netconfigs_id
FROM netconfigs JOIN offices ON offices.id = netconfigs.id_office JOIN
computers ON offices.id = computers.id_office
WHERE (netconfigs.id_office = %s AND (computers.id_netconfig IS NULL))
Regards,
--
Alexandre CONRAD
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
