Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 1.2.3
-
Fix Version/s: None
-
Component/s: Query
-
Labels:None
-
Environment:PHP Version 5.3.3-1ubuntu9.3; Apache/2.2.16 (Ubuntu); MySQL: 5.1.41; Symfony 1.4; Doctrine 1.2.3 (Revision: 7490)
Description
I'm using Doctrine with Symfony and i have some troubles when i'm using the mysql command: interval. Here's a detailed explanation:
Part of the schema:
User:
columns:
name: { type: string(255), notnull: true }
nick: { type: string(255), notnull: true, unique: true }
email: { type: string(255), notnull: true, unique: true }
password: { type: string(40), notnull: true }
VirtualConferenceRoom:
columns:
adminId: { type: integer, notnull: true }
name: { type: string(255), notnull: true }
startDate: { type: timestamp, notnull: true }
duration: { type: integer, notnull: true }
relations:
Admin:
class: User
foreign: id
local: adminId
VirtualConferenceRoomUser:
columns:
virtualConferenceRoomId: { type: integer, notnull: true }
userId: { type: integer, notnull: true }
relations:
VirtualConferenceRoom:
class: VirtualConferenceRoom
foreign: id
local: virtualConferenceRoomId
User:
class: User
foreign: id
local: userId
I've generated my model with symfony's built in command(symfony doctrine:build --model --sql)
When I try to get the conference rooms which are in progress at the moment I try it like this:
// In the project/lib/model/doctrine/VirtualConferenceRoomTable.class.php which is extends from Doctrine_Table public function getRoomsInProgressByUserId($userId) { return Doctrine_Query::create() ->select('vcr.*') ->from('VirtualConferenceRoom vcr') ->innerJoin('vcr.VirtualConferenceRoomUser vcru') ->where('(vcr.StartDate < now()) and (vcr.StartDate + interval vcr.Duration minute > now()) and vcru.UserId = ?', array($userId)) ->execute(); }
When I try to run my app I get an error message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vcr.Duration' in 'where clause'"
The generated sql command is:
" SELECT v.id AS v__id, v.userid AS v__userid, v.name AS v__name, v.startdate AS v__startdate, v.duration AS v__duration FROM virtual_conference_room v INNER JOIN virtual_conference_room_user v2 ON v.id = v2.virtualconferenceroomid WHERE (v.startdate < NOW() AND v.startdate + interval vcr.Duration minute > NOW() AND v2.userid = ?) - (2)"
The problem is the vcr.Duration section. If I change my function from:
->where('(vcr.StartDate < now()) and (vcr.StartDate + interval vcr.Duration minute > now()) and vcru.UserId = ?', array($userId))
to:
->where('(vcr.StartDate < now()) and (vcr.StartDate + interval v.Duration minute > now()) and vcru.UserId = ?', array($userId))
it works perfectly.
I hope my report is useful and clear and i can help you to improve this great ORM 
Best wishes: Tibor Erdész <erdeszt@gmail.com>
Just figured out that I can use
notations and I fixed my report.