Further to my original message, I have managed to work some of the problem
out...
creating a function in the BookingPeer.php file...
This current version uses no passed criteria for the current date or date
selected by the user in the view...
function getBookedUnbooked()
{
$con = Propel::getConnection();
$sql = "
SELECT booking.* FROM booking
WHERE '2008-06-12' BETWEEN start_date AND end_date
UNION
SELECT 0 as id, 0 as client_id, id AS room_id, '0000-00-00' as
start_date, '0000-00-00' as end_date, 0 as adults, 0 as children, '0000-00-00
00:00:00' as created_at FROM room
WHERE room.id NOT IN (
SELECT room_id FROM booking
WHERE '2008-06-12' BETWEEN start_date
AND end_date
)
ORDER BY room_id
";
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
return BookingPeer::populateObjects($rs);
} // end function
Next step is to add the passed criteria...
Also, is there a way to do the above using the
addCriteria/addAscendingOrderByColumn and other Propel capabilities? It would
be nicer than using the above just in case the structure of the database
changed!
Adrian
-----Original Message-----
From: Gould, Adrian
Sent: Fri 6/13/2008 13:52
To: Symfony Googlegroup
Subject: Complex criteria creation - hotel room bookings problem
Hi all
I am trying to create the following SQL as a PROPEL criteria to sit in the
Bookings model.
Details are:
Schema------
booking:
id: ~
client_id: integer
room_id: integer
start_date: date
end_date: date
adults: integer
children: integer
created_at: timestamp
client:
id: ~
name: varchar(128)
created_at: timestamp
room:
id: ~
name: varchar(128)
price: decimal
bed: integer
created_at: timestamp
SQL-----
[with checkDate being the date to be checked that is passed from the view]
SELECT room_id, 'Y' AS booked FROM booking
WHERE checkDate BETWEEN start_date AND end_date
UNION
SELECT id AS room_id, 'N' AS booked FROM room
WHERE id NOT IN (
SELECT room_id FROM booking
WHERE checkDate BETWEEN start_date AND end_date
)
ORDER BY room_id
The eventual purpose is to display a table in the view with rooms and dates
booked:
Room
Date 101 102 103 201 202 203
20080612 Y N Y N Y N
20080613 Y N N N Y Y
20080614 N Y Y N Y Y
20080615 N N Y N Y N
Now I am making an assumption that this is best placed in the Booking.php file
in lib/model so that results are available to more than one controller.
I am not that familiar with PROPEL yet, so help is appreciated.
Once I have this little application working I am aiming to provide it to the
symfony community as a tutorial via our IT web site, so help will be credited
both here and in the articles.
Adrian
---
Adrian Gould
Lecturer in IT / Network Engineering / Multimedia
Business Finance & Computing [Midland Campus]
Swan TAFE
PO BOX 1336, Midland WA 6936
Phone: (08) 9267 7777
eMail: [EMAIL PROTECTED]
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"symfony users" 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/symfony-users?hl=en
-~----------~----~----~----~------~----~------~--~---
