You do not tell us which field belongs to which table so making some
guesses...
And setting this aside for a moment...
installation.install_date = (select max(install_date) from
installation where application_id=application.id and
server_id=server.id)
This is your query
where server.customer_id=customer.id
and server.server_type_id=server_type.id
and application.customer_id=customer.id
and installation.server_id=server.id
and installation.application_id=application.id
which maps into DAL:
(db.server.customer_id==db.customer.id)&(server.server_type_id==db.server_type.id)&(db.application.customer_id==db.customer.id)&(db.installation.server_id==db.server.id)&(db.installation.application_id==db.application.id)
Therefore
db((db.server.customer_id==db.customer.id)&(server.server_type_id==db.server_type.id)&(db.application.customer_id==db.customer.id)&(db.installation.server_id==db.server.id)&(db.installation.application_id==db.application.id)).select(
db.customer.name,
db.server.name,
db.server.type_name,
db.application.name,
db.application.version,
db.installation.install_date,
orderby=db.customer.name|db.server.name|db.application|name)
Now this
installation.install_date = (select max(install_date) from
installation where application_id=application.id and
server_id=server.id)
I am not sure because I cannot try it and I do not know your table
structure but I guess:
from gluon.dal import Expression
condition = (db.installation.install_date ==
Expression(db,db(db.application_id==db.application.id)
(db.server_id==db.server.id)._select(db.installation.install_date.max()))
so finally try this:
from gluon.dal import Expression
condition = (db.installation.install_date ==
Expression(db,db(db.application_id==db.application.id)
(db.server_id==db.server.id)._select(db.installation.install_date.max()))
rows =
db((db.server.customer_id==db.customer.id)&(server.server_type_id==db.server_type.id)&(db.application.customer_id==db.customer.id)&(db.installation.server_id==db.server.id)&(db.installation.application_id==db.application.id)&condition).select(
db.customer.name,
db.server.name,
db.server.type_name,
db.application.name,
db.application.version,
db.installation.install_date,
orderby=db.customer.name|db.server.name|db.application|name)
On Dec 6, 8:33 am, SimMode <[email protected]> wrote:
> All,
>
> How can I formulate this query using dal objects?
>
> select
> customer_name,
> server_name,
> server_type_name,
> application_name,
> application_version,
> install_date
> from customer, server, server_type, application, installation
> where server.customer_id=customer.id
> and server.server_type_id=server_type.id
> and application.customer_id=customer.id
> and installation.server_id=server.id
> and installation.application_id=application.id
> and installation.install_date =
> (select max(install_date) from installation where
> application_id=application.id and server_id=server.id)
> order by customer_name, server_name, application_name
>
> Thanks
> Simmode
