I have an issue with a doctrine query to the database and I'm not sure what is wrong. here's the deal: this is the actual mysql query I used to create the database:
create table productos (
codigoProducto int (8) unsigned auto_increment primary key,
nombre varchar(100) not null,
id_categoria smallint(3) unsigned,
descripcion text,
foto varchar(100),
precio smallint (4) unsigned not null,
visitado int (7),
habilitado boolean,
foreign key (id_categoria)
references categorias(id)
on update cascade
on delete restrict
)engine=innoDB;
create table facturas(
id int (8) unsigned auto_increment primary key,
id_comprador int (7) unsigned,
metodo_pago boolean,
forma_entrega boolean,
fecha datetime,
foreign key (id_comprador)
references compradores(id)
on update cascade
on delete cascade
)engine=innoDB;
create table detallefactura(
id int(8) unsigned auto_increment primary key,
id_producto int(8) unsigned,
id_factura int(8) unsigned,
cantidad smallint (4),
foreign key(id_producto)
references productos(codigoProducto)
on update cascade
on delete cascade,
foreign key(id_factura)
references facturas(id)
on update cascade
on delete cascade
)engine=innoDB;
"detalleFactura" is the join table for 'productos' and 'facturas', in order to have more than 1 product per invoice. So with the following I'm trying to take all products bought in one invoice:
static function traerProductosComprados($id){
$query = Doctrine_Query::create()
->select('d.id, p.nombre, p.precio, d.cantidad')
->from('Detallefactura d')
->leftjoin('d.Productos p')
->where('d.id_factura = ?',$id);
return $query->execute()->toArray();
}
if I make an echo of the query, it shows the following:
SELECT d.id, p.nombre, p.precio, d.cantidad FROM Detallefactura d LEFT JOIN d.Productos p WHERE d.id_factura = ?
when I paste this to the phpmyadmin, it throws the following error:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE d.id_factura = ? LIMIT 0, 30' at line 1;
I'm out of ideas now, please tell me if there's something else I'm missing!
You need to specify ON clause for your left join