Databases
StackOverflow2013
Postsdbo
POMySQL / doctrine issue with a left join
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL / doctrine issue with a left join
    primarykey
    data
    text
    <p>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:</p> <pre><code> 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; </code></pre> <p>"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:</p> <pre><code> static function traerProductosComprados($id){ $query = Doctrine_Query::create() -&gt;select('d.id, p.nombre, p.precio, d.cantidad') -&gt;from('Detallefactura d') -&gt;leftjoin('d.Productos p') -&gt;where('d.id_factura = ?',$id); return $query-&gt;execute()-&gt;toArray(); } </code></pre> <p>if I make an echo of the query, it shows the following:</p> <pre><code> SELECT d.id, p.nombre, p.precio, d.cantidad FROM Detallefactura d LEFT JOIN d.Productos p WHERE d.id_factura = ? </code></pre> <p>when I paste this to the phpmyadmin, it throws the following error:</p> <blockquote> <p>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;</p> </blockquote> <p>I'm out of ideas now, please tell me if there's something else I'm missing!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USacme
    1. USJuan Ignacio Gonzalez Nadin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL / doctrine issue with a left join
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODoctrine show you an error? How do you *echo* the query?
      singulars
      1. POMySQL / doctrine issue with a left join
      1. USj0k
    2. COThat query that you echoed is a DQL statement. It won't work in PhpMyAdmin, which only speaks SQL.
      singulars
      1. POMySQL / doctrine issue with a left join
      1. USRadu C
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload