Is this possible to join tables in doctrine ORM without using relations?
- by piemesons
Suppose there are two tables.
Table X--
Columns:
id x_value
Table Y--
Columns:
id x_id y_value
Now I dont want to define relationship in doctrine classes and i want to retrieve some records using these two tables using a query like this:
Select x_value from x, y where y.id="variable_z" and x.id=y.x_id;
I m not able to figure out how to write query like this in doctrine orm
EDIT:
Table structures:
Table 1:
CREATE TABLE IF NOT EXISTS `image` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`random_name` varchar(255) NOT NULL,
`user_id` int(11) NOT NULL,
`community_id` int(11) NOT NULL,
`published` varchar(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=259 ;
Table 2:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`city` varchar(20) DEFAULT NULL,
`state` varchar(20) DEFAULT NULL,
`school` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;
Query I am using:
$q = new Doctrine_RawSql();
$q ->select('{u.*}, {img.*}')
->from('users u LEFT JOIN image img ON u.id = img.user_id')
->addComponent('u', 'Users u')
->addComponent('img', 'u.Image img')
->where("img.community_id='$community_id' AND img.published='y' AND u.state='$state' AND u.city='$city
->orderBy('img.id DESC')
->limit($count+12)
->execute();
Error I am getting:
Fatal error: Uncaught exception 'Doctrine_Exception' with message 'Couldn't find class
u' in C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\Table.php:290 Stack trace: #0
C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\Table.php(240): Doctrine_Table- >initDefinition() #1 C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\Connection.php(1127):
Doctrine_Table->__construct('u', Object(Doctrine_Connection_Mysql), true) #2
C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\RawSql.php(425): Doctrine_Connection-
>getTable('u') #3 C:\xampp\htdocs\fanyer\doctrine\models\Image.php(33): Doctrine_RawSql-
>addComponent('img', 'u.Image imga') #4 C:\xampp\htdocs\fanyer\community_images.php(31):
Image->get_community_images_gallery_filter(4, 0, 'AL', 'ALBERTVILLE') #5 {main} thrown in
C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\Table.php on line 290
