Hello everyone?
I need to translate following complex query into SQLA. How do I do that?
SELECT h.id AS hosts_id, h.ip AS hosts_ip, h.hostname AS hosts_hostname,
h.location AS hosts_location, h.architecture_id AS
hosts_architecture_id, h.os_kind_id AS hosts_os_kind_id, h.os_version_id
AS hosts_os_version_id, h.additional_info AS hosts_additional_info,
h.column_12 AS hosts_column_12, h.column_13 AS hosts_column_13,
h.username AS hosts_username, h.password AS hosts_password, h.alias AS
hosts_alias, h.virtualization_id AS hosts_virtualization_id, h.shareable
AS hosts_shareable, h.shareable_between_projects AS
hosts_shareable_between_projects, h.notes AS hosts_notes, h.cpu AS
hosts_cpu, h.ram AS hosts_ram, h.column_24 AS hosts_column_24, h.batch
AS hosts_batch, h.asset AS hosts_asset, h.owner AS hosts_owner,
h.ssh_key_present AS hosts_ssh_key_present, h.machine_type_model AS
hosts_machine_type_model, h.mac_address_eth_0 AS
hosts_mac_address_eth_0, h.physical_box AS hosts_physical_box,
h.up_n_running AS hosts_up_n_running, h.available AS hosts_available,
h.project_id AS hosts_project_id, architecture.id AS architecture_id,
architecture.architecture AS architecture_architecture, os_kind.id AS
os_kind_id, os_kind.os_kind AS os_kind_os_kind, os_version.id AS
os_version_id, os_version.os_version AS os_version_os_version,
virtualization.id AS virtualization_id, virtualization.virtualization AS
virtualization_virtualization, virtualization.color AS
virtualization_color, project.id AS project_id, project.project AS
project_project, email.id AS email_id, email.email AS email_email,
rh.reservation_id AS reservation_id, r.start_date AS
reservation_start_date, r.end_date AS reservation_end_date, r.status AS
reservation_status, r.businessneed AS reservation_businessneed,
r.notetohwrep AS reservation_notetohwrep, r.email_id AS
reservation_email_id, r.project_id AS reservation_project_id
FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON h.id = rh.host_id
LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id
INNER JOIN
(SELECT h.id, MIN(r.start_date) FROM hosts h
LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
LEFT OUTER JOIN reservation r ON rh.reservation_id = r.id GROUP BY h.id
ORDER BY h.id) AS min_date(m_host_id, m_start_date)
ON h.id = min_date.m_host_id AND (r.start_date = min_date.m_start_date
AND (r.start_date,r.end_date) OVERLAPS ('2009-04-10'::date,
'2010-04-10'::date) OR min_date.m_start_date IS NULL)
INNER JOIN architecture ON architecture.id = h.architecture_id
INNER JOIN os_kind ON h.os_kind_id = os_kind.id
INNER JOIN os_version ON h.os_version_id = os_version.id
INNER JOIN virtualization ON h.virtualization_id = virtualization.id
INNER JOIN project ON h.project_id = project.id AND project.project = 'LMT'
LEFT OUTER JOIN email ON r.email_id = email.id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date <= '2009-04-10'::date AND r.end_date > '2009-04-11'::date)
ORDER BY h.id, r.start_date)
AND h.up_n_running = True AND h.shareable = True ORDER BY virtualization
DESC, ip ASC
Regards,
mk
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
