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

Adam · Accepted Answer · 2009-08-16 10:20:23Z

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

answered Aug 16, 2009 at 10:20

Adam

8031 gold badge8 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheCodeArtist · Accepted Answer · 2015-04-18 04:14:41Z

1

After 2 hours of digging and asking myself why I am not getting expected results, I realised that __contains is case-sensitive 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".

edited Apr 18, 2015 at 4:14

TheCodeArtist

22.6k4 gold badges74 silver badges135 bronze badges

answered Aug 18, 2009 at 6:22

Mohamed

3,6407 gold badges30 silver badges31 bronze badges

Collectives™ on Stack Overflow

django sql join with like

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related