The World’s Largest Online Community for Developers
I'm having a hard time figuring why I'm receiving the below error when the SQL I'm aiming for seems valid. Any ideas? Thank you!
sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.
Here's my raw SQL query in question:
entries = Entry.objects.raw("SELECT * FROM learning_logs_entry LEFT JOIN learning_logs_document ON (learning_logs_entry.id = learning_logs_document.entry_id) WHERE learning_logs_entry.topic_id = %s AND learning_logs_entry.tag_id IN %s ORDER BY learning_logs_entry.date_added DESC", params=[topic_id, (1,2,3)])
Here's the SQL query produced:
SELECT * FROM learning_logs_entry LEFT JOIN learning_logs_document ON (learning_logs_entry.id = learning_logs_document.entry_id) WHERE learning_logs_entry.topic_id = 1 AND learning_logs_entry.tag_id IN (1, 2, 3) ORDER BY learning_logs_entry.date_added DESC
Here's my model for both Entry & Tag:
class Entry(models.Model): topic = models.ForeignKey(Topic,on_delete=models.CASCADE, related_name='entries') text = models.TextField(validators=[validate_text]) date_added = models.DateTimeField(auto_now_add=True) tag = models.ForeignKey(Tag, on_delete=models.CASCADE) user = models.ForeignKey(User, on_delete=models.CASCADE) class Meta: #metadata verbose_name_plural = 'entries' class Tag(models.Model): text = models.CharField(max_length=200) date_added = models.DateTimeField(auto_now_add=True) class Document(models.Model): docfile = models.ImageField(upload_to='files/', blank=True, null=True) entry = models.ForeignKey(Entry, on_delete=models.CASCADE, null=True)
Please do not make use of raw queries, unless it is something that can not be done (efficiently) with the Django ORM.
This however can be done effectively with the Django ORM, for example with:
Entry.objects.filter( tag_id__in=[1,2,3] topic_id=topic_id ).prefetch_related('document_set').order_by('-date_added')
.prefetch_related will make an extra query, but this will reduce the amount of bandwidth, since if this is done in a JOIN, the same record of entry can be returned multiple times, that could result in transmitting the same data hundreds of times.