When I try this I get the following:
<class 'gluon.contrib.pymysql.err.ProgrammingError'> (1064, u"You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near ';))) ORDER BY
customer.customer_name, server.server_name, application.applicatio' at
line 1")
Here's my code:
from gluon.dal import Expression
condition = (db.installation.install_date ==
Expression(db,db((db.installation.application_id==db.application.id)
&
(db.installation.server_id==db.server.id))._select(db.installation.install_date.max())))
query = ((db.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)
rows = db(query).select(db.customer.customer_name,
db.server.server_name,
db.server_type.server_type_name,
db.application.application_name,
db.installation.application_version,
db.installation.install_date,
orderby=db.customer.customer_name|db.server.server_name|
db.application.application_name)
BTW here's my table structure:
db.define_table('customer',
Field('customer_name', unique=True),
format="%(customer_name)s")
db.define_table('server_type',
Field('server_type_name'),
format="%(server_type_name)s")
db.define_table('server',
Field('customer_id', db.customer, label="Customer"),
Field('server_type_id', db.server_type, label="Server Type"),
Field('server_name', length=30),
format="%(server_name)s")
db.define_table('application',
Field('customer_id', db.customer, label="Customer"),
Field('application_name'),
format="%(application_name)s")
db.define_table('installation',
Field('application_id', db.application, label="Application"),
Field('server_id', db.server, label="Server"),
Field('install_date', 'date'),
Field('remove_date', 'date'),
Field('application_version'),
format="%(server_name)s")
On Dec 6, 11:11 am, Massimo Di Pierro <[email protected]>
wrote:
> 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
