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.
