Hello, I have two classes: Host, Reservation (in many-to-many relation), and three tables: hosts, host_reservation (association table), reservation.
I need to get table of pairs (host, reservation) *where host is unique and (reservation for this host that has the earliest date OR None in place of reservation)*. If I do: s=session.query(Host, Reservation).outerjoin(Host.reservations).order_by(Host.id, Reservation.start_date) ..that works, but produces more than 1 (earliest) reservation for a given host: 2009-04-08 14:46:47,375 INFO sqlalchemy.engine.base.Engine.0x...3c94 SELECT hosts.id AS hosts_id, hosts.ip AS hosts_ip, hosts.hostname AS hosts_hostname, hosts.location AS hosts_location, hosts.architecture_id AS hosts_architecture_id, hosts.os_kind_id AS hosts_os_kind_id, hosts.os_version_id AS hosts_os_version_id, hosts.additional_info AS hosts_additional_info, hosts.column_12 AS hosts_column_12, hosts.column_13 AS hosts_column_13, hosts.username AS hosts_username, hosts.password AS hosts_password, hosts.alias AS hosts_alias, hosts.virtualization_id AS hosts_virtualization_id, hosts.shareable AS hosts_shareable, hosts.shareable_between_projects AS hosts_shareable_between_projects, hosts.notes AS hosts_notes, hosts.cpu AS hosts_cpu, hosts.ram AS hosts_ram, hosts.column_24 AS hosts_column_24, hosts.batch AS hosts_batch, hosts.asset AS hosts_asset, hosts.owner AS hosts_owner, hosts.ssh_key_present AS hosts_ssh_key_present, hosts.machine_type_model AS hosts_machine_type_model, hosts.mac_address_eth_0 AS hosts_mac_address_eth_0, hosts.physical_box AS hosts_physical_box, hosts.up_n_running AS hosts_up_n_running, hosts.available AS hosts_available, hosts.earliest_reservation_id AS hosts_earliest_reservation_id, hosts.project_id AS hosts_project_id, reservation.id AS reservation_id, reservation.start_date AS reservation_start_date, reservation.end_date AS reservation_end_date, reservation.status AS reservation_status, reservation.businessneed AS reservation_businessneed, reservation.notetohwrep AS reservation_notetohwrep, reservation.email_id AS reservation_email_id, reservation.project_id AS reservation_project_id FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON hosts.id = reservation_hosts_1.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts_1.reservation_id ORDER BY hosts.id, reservation.start_date (host.id, reservation.start_date) 344 2009-04-13 344 2009-04-20 354 2009-04-13 354 2009-04-20 355 2009-04-13 355 2009-04-20 ... I need to get the table like following: (host.id, reservation.start_date) 344 2009-04-13 354 2009-04-13 355 2009-04-13 I managed to produce plain SQL (Postgres) query producing the result I need, but I have no idea how to translate that into SQLA: SELECT hosts.id AS hosts_id, hosts.ip AS hosts_ip, hosts.hostname AS hosts_hostname, hosts.location AS hosts_location, hosts.architecture_id AS hosts_architecture_id, hosts.os_kind_id AS hosts_os_kind_id, hosts.os_version_id AS hosts_os_version_id, hosts.additional_info AS hosts_additional_info, hosts.column_12 AS hosts_column_12, hosts.column_13 AS hosts_column_13, hosts.username AS hosts_username, hosts.password AS hosts_password, hosts.alias AS hosts_alias, hosts.virtualization_id AS hosts_virtualization_id, hosts.shareable AS hosts_shareable, hosts.shareable_between_projects AS hosts_shareable_between_projects, hosts.notes AS hosts_notes, hosts.cpu AS hosts_cpu, hosts.ram AS hosts_ram, hosts.column_24 AS hosts_column_24, hosts.batch AS hosts_batch, hosts.asset AS hosts_asset, hosts.owner AS hosts_owner, hosts.ssh_key_present AS hosts_ssh_key_present, hosts.machine_type_model AS hosts_machine_type_model, hosts.mac_address_eth_0 AS hosts_mac_address_eth_0, hosts.physical_box AS hosts_physical_box, hosts.up_n_running AS hosts_up_n_running, hosts.available AS hosts_available, hosts.earliest_reservation_id AS hosts_earliest_reservation_id, hosts.project_id AS hosts_project_id, reservation.id AS reservation_id, reservation.start_date AS reservation_start_date, reservation.end_date AS reservation_end_date, reservation.status AS reservation_status, reservation.businessneed AS reservation_businessneed, reservation.notetohwrep AS reservation_notetohwrep, reservation.email_id AS reservation_email_id, reservation.project_id AS reservation_project_id FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON hosts.id = reservation_hosts_1.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts_1.reservation_id INNER JOIN (SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id GROUP BY rh.host_id) AS min_date(host_id, start_date) ON hosts.id = min_date.host_id AND reservation.start_date = min_date.start_date WHERE reservation.id IS NOT NULL ORDER BY hosts.id, reservation.start_date Anybody has an idea? Regards, mk --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
