id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
5295	Inner Join and order_by bug	MarioGonzalez <gonzalemario @…>	anonymous	"I found a problem using an inner join; these are some of my classes:


{{{
class Egreso(models.Model):
	id_egreso = models.AutoField(primary_key=True, db_index=True, db_column=""id_egreso"")
	id_usuario = models.ForeignKey(Usuario, db_index=True, db_column=""id_usuario"")	
	fecha_ingreso = models.DateTimeField(auto_now=True)

class Proveedor_combustible(models.Model):
	nombre = models.CharField(maxlength=200, unique=True)

	def __unicode__(self):
		return self.nombre

class Combustible(models.Model):
	id_egreso = models.ForeignKey(Egreso, db_index=True, db_column=""id_egreso"")
	numero_factura = models.PositiveIntegerField(null=True)
	proveedor = models.ForeignKey(Proveedor_combustible, db_column=""proveedor"")
	precio = models.CharField(maxlength=50)

	def __unicode__(self):
		return self.numero_factura

	class Meta:
		unique_together = ('numero_factura', 'proveedor'),
}}}

   I want to execute a inner join and ordering the results using the right table, I always just did raw queries but now I tried to understand better the django api. This is the query I executed: 

{{{
Combustible.objects.filter(id_egreso__id_usuario=id_usuario).order_by('gestion_combustible.fecha_ingreso')
}}}
 

However it fails because Django use an alias for the inner join; here is the SQL query that Django shows:


{{{
SELECT ""gestion_combustible"".""id"",""gestion_combustible"".""id_egreso"",""gestion_combustible"".""numero_factura"",""gestion_combustible"".""proveedor"",""gestion_combustible"".""precio""
FROM ""gestion_combustible""
   INNER JOIN ""gestion_egreso"" AS ""gestion_combustible__id_egreso""
   ON ""gestion_combustible"".""id_egreso"" = ""gestion_combustible__id_egreso"".""id_egreso""
WHERE (""gestion_combustible__id_egreso"".""id_usuario"" = '1')
ORDER BY ""gestion_egreso"".""fecha_ingreso"" ASC
}}}


 As you can see the 2nd table is called gestion_egreso but the alias is called gestion_combustible_id_egreso so the ORDER BY should be done using the alias name not the field name. So far, the field name is used so it fails. The ""solution"": if you're using inners joins use the alias name in the order by.

 But in my opinion, this must be done in the background. 
"		closed	Database layer (models, ORM)	dev		fixed	qs-rf-fixed	gonzalemario@…	Accepted	1	0	1	0	0	0