This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use the new forum.
Home » support » symfony 1.3 and 1.4 » doctrine / createQuery / leftjoin
icon5.gif  doctrine / createQuery / leftjoin [message #95685] Thu, 18 March 2010 11:47 Go to next message
xac369  is currently offline xac369
Messages: 7
Registered: March 2010
Junior Member
Hi !

I'm working on a timesheet project and got problems with queries.

config\doctrine\schema.yml
Project:
  actAs: { Timestampable: ~ }
  columns:
    client_id:  { type: integer, notnull: true }
    name:      { type: string(255), notnull: true }
    isopen:    { type: boolean, notnull: true, default: 1 }
    startdate:    { type: date }
    enddate:    { type: date }
  relations:
    Client: { local: client_id, foreign: id, foreignAlias: Projects } 
    
TimesheetForecast:
  actAs: { Timestampable: ~ }
  columns:
    group_id: { type: integer(4), notnull: true }
    project_id:  { type: integer, notnull: true }
    user_id:  { type: integer(4), notnull: true }
    hours: { type: float }
    tdate: { type: date }
    cost: { type: int }
    isvalidated: { type: boolean, notnull: true, default: 0 }
    validatedby: { type: int }
  relations:
    sfGuardUserGroup:
      class: sfGuardGroup
      local: group_id
      foreign: id
      foreignType: one
      foreignAlias: UserGroup 
    Project: { local: project_id, foreign: id, foreignAlias: Project }
    sfGuardUser:
      class: sfGuardUser
      local: user_id
      foreign: id
      foreignType: one
      foreignAlias: User    


I'd like to create this query in my ProjectTable.class.php

  	select distinct project.* 
	from project
	left join timesheet_forecast
	on project.id = timesheet_forecast.project_id
	where timesheet_forecast.user_id = 1



\lib\model\doctrine\ProjectTable.class.php
  public function getProjectUserlist($userid) {
     $q = Doctrine_Query::create()
		->from('project')
		->leftJoin('timesheet_forecast on project.id = timesheet_forecast.project_id')
		->where('timesheetForecast.user_id = ?',$userid);
	
     return $q->execute();
  }


Got this error
Couldn't find class timesheet_forecast


I test lot of things :
$q = Doctrine_Query::create()
		->from('Project')
		->leftJoin('timesheetForecast on Project.id = timesheetForecast.project_id')
		->where('timesheetForecast.user_id = ?',$userid);


$q = $this->createQuery('p')
->leftJoin('timesheetForecast on Project.id = timesheetForecast.project_id')
		->where('timesheetForecast.user_id = ?',$userid);


and lot ...

But I don't understand, how the relation is made between classes/ table name.

Class Project and timesheetForecast exists. The relation is made on timesheetForecast to Project.

Find in attachment diagram of database.

I read the doctrine documentation, with examples user/phonenumbers, but is not really clear.
http://www.doctrine-project.org/documentation/manual/1_0/en/ dql-doctrine-query-language

Could you help me to create query in my class Project with a join on my table timesheet_forecast ? What is the right method, class name/table name ?

Thanks,
x

  • Attachment: timesheet.jpg
    (Size: 36.98KB, Downloaded 59 time(s))

Re: doctrine / createQuery / leftjoin [message #95693 is a reply to message #95685 ] Thu, 18 March 2010 12:53 Go to previous messageGo to next message
xac369  is currently offline xac369
Messages: 7
Registered: March 2010
Junior Member
To precise my request.

//works :
$q = Doctrine_Query::create()
->select( '*' )
->from( 'timesheetForecast tf' )
->leftJoin('tf.Project p');
$v = $q->execute(); 


This code works in the wrong way : i want to list Project in relation with timesheetForecast.
But the foreign key is in timesheetForecast, and not in Project.

Is really simple in SQL but too complexe in Doctrine ?

Re: doctrine / createQuery / leftjoin [message #95708 is a reply to message #95685 ] Thu, 18 March 2010 17:40 Go to previous messageGo to next message
Levelx  is currently offline Levelx
Messages: 358
Registered: April 2009
Location: Germany
Faithful Member
Change this
    Project: { local: project_id, foreign: id, foreignAlias: Project }

to something like
    Project: { local: project_id, foreign: id, foreignAlias: TimesheetForecasts }

because the foreignAlias is the relation looking from the project.


Then your query should look like this:

$q = Doctrine_Query::create()
 ->from( 'Project p' )
 ->leftJoin('p.TimesheetForecasts tf');
$v = $q->execute(); 


icon7.gif  Re: doctrine / createQuery / leftjoin [message #95712 is a reply to message #95708 ] Thu, 18 March 2010 18:34 Go to previous messageGo to next message
xac369  is currently offline xac369
Messages: 7
Registered: March 2010
Junior Member
Oh ! Now I understood "foreignAlias" Smile

I guess, I put "TimesheetForecasts" on all my relations like a virtual field in destination table (sfGuardUserGroup, Project, sfGuardUser). Is it correct ?

TimesheetForecast:
  actAs: { Timestampable: ~ }
  columns:
    project_id:  { type: integer, notnull: true }
    user_id:  { type: integer(4), notnull: true }
    group_id: { type: integer(4), notnull: true }
    hours: { type: float }
    tdate: { type: date }
    cost: { type: int }
    isvalidated: { type: boolean, notnull: true, default: 0 }
    validatedby: { type: int }
  relations:
    sfGuardUserGroup:
      class: sfGuardGroup
      local: group_id
      foreign: id
      foreignAlias: TimesheetForecasts
    Project:
      local: project_id
      foreign: id
      foreignAlias: TimesheetForecasts
    sfGuardUser:
      class: sfGuardUser
      local: user_id
      foreign: id
      foreignAlias: TimesheetForecasts



I found another topic with explanations of "foreignAlias" and naming :
http://forum.symfony-project.org/index.php/m/92768/

Thanks a lot Levelx ! Smile
Re: doctrine / createQuery / leftjoin [message #103276 is a reply to message #95712 ] Fri, 30 July 2010 10:31 Go to previous message
ruanchao  is currently offline ruanchao
Messages: 11
Registered: August 2009
Location: China
Junior Member
Thank you Mad
Previous Topic:EmbeddedForm validatorSchema in bind method
Next Topic:[SOLVED] Variables accessible from every modules action and template
Goto Forum:
  

powered by FUDforum - copyright ©2001-2004 FUD Forum Bulletin Board Software