django sql join with like

Question

I have following models

class Artist(models.Model):
    name = models.CharField(max_length=200, unique=True)
    photo = models.CharField(max_length=250)
    views = models.IntegerField()

    class Meta:
        verbose_name = "artist"
        ordering = ['-views']

    def __unicode__(self):
        return self.name

class Song(models.Model):
    artist = models.ForeignKey("Artist")
    name = models.CharField(max_length=250)
    lyrics = models.TextField(max_length=255)
    path = models.CharField(max_length=200)
    views = models.IntegerField()
    date = models.DateField()

    class Meta:
        verbose_name = "song"
        ordering = ['-views']

    def __unicode__(self):
        return self.name

and would like to get all the rows that match the keyword. I can do it in sql and returns what I want. here is the sql;

SELECT song.name, song.path FROM song JOIN artist on song.artist_id = artist.id WHERE artist.name LIKE %keyword% || song.name LIKE %keyword% || song.lyrics LIKE %keyword%

but could not figure out how to do that in django. should I use custom sql? do you guys have better idea?

thanks in advance

Answer 1

matching_songs = Song.objects.filter(Q(name__contains=keyword) | Q(lyrics__contains=keyword) | Q(artist__name__contains=keyword))

See Django docs on Q objects for more details

Answer 2

After 2 hours of digging and asking my self why I am not getting expected results, I realised that __contains is case sensetive while __icontains is not. so, for example if we have person named torres in database and supply Torres to __contains then it will return nothing while __icontains will return Torres.