tl;dr can anyone point me towards how I can ask the ORM to spit out just a table’s name, not table.column, in a SQL expression?
I’ve built some infrastructure around SQLite’s FTS5 system for a small, personal app. (I tried to link it, but as a new user, I’m only allowed 2 links which I’m spending elsewhere in this post. sqlite.org/fts5.html will get you there.) The design is roughly:
- A manual migration which creates the virtual table (code listing 1)
- An unmanaged table wrapping it (2)
- A method to perform a search (3)
- Infrastructure for keeping the FTS index up to date (outside the scope of what’s I’m asking for here)
My original search method used .raw() to invoke FTS5’s table-valued syntax: SELECT * FROM search_v4(search_terms *);. This gets me a RawQueryset, which is poorly-featured compared to a full Queryset (e.g. I can’t do things like .first() or take slices) and has some more quirks (one I recall is having to write SELECT rowid, * ... or else Paginator didn’t work, though I may be misremembering that).
My wrapper model should be capable of fully treating the virtual table as a (read-only) regular table, though, and that would get me a full Queryset. I went down a rabbit hole trying to get Django ORM to emit either table-valued function syntax or, as a fallback, FTS5’s other syntax: SELECT * FROM table WHERE table = search_terms. I want to be able to write SearchDoc.objects.table_name('search terms') or, in fallback terms, to something like SearchDoc.objects.filter(search_v4='search terms').
The ORM really wants to express column references, not table references. I could not figure out how to write a standalone Expression or other Query API object that would give me the table name in the right place, nor how to plug it into the object manager as .table_name(). In my fallback syntax, search_v4 isn’t a known field name, so that also fails.
I ended up with a hack: a Lookup which eats the field name in its as_sql method. This feels extremely brittle in ways I can’t quite put my finger on, and it’s unergonomic. To use it, you pick any arbitrary field knowing that it’ll be thrown away: SearchDoc.objects.filter(body__table_match='search terms'). It generates the table = search_terms version of the SQL. The code listing for this hack is shown below. I’d prefer to replace .filter() with some kind of .table_name() construct, or failing that, allow search_v4= (or more generically, perhaps _table_name=) as an argument to filter.
Can anyone point me in the right direction for where I’d look to inject this capability? To be clear, I’m not proposing Django adopt this as a core feature, I’m just looking to solve my problem with the least-hacky thing. It feels like between Expression and Lookup and all the other first-class ways to extend Django’s SQL generation, I must be missing something simple.
Code snippets:
-- 1: virtual table for FTS5
CREATE VIRTUAL TABLE search_v4 USING fts5(
body,
object_id UNINDEXED,
content_type_id UNINDEXED,
tokenize = 'trigram'
);
// 2 - unmanaged wrapper table
class SearchDoc(models.Model):
class Meta:
managed = False
db_table = 'search_v4'
id = models.IntegerField(db_column='rowid', primary_key=True)
# search document
body = models.TextField()
# search result rank
rank = models.FloatField(null=True)
# next 3 are links to searchable entities
content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
object_id = models.PositiveBigIntegerField()
content_object = GenericForeignKey("content_type", "object_id")
def __str__(self):
return f'"{self.body.replace('\n', '\\n')}"'
// 3 - current search implementation
def search(raw_terms: str):
# see the SQLite fts5 docs about FTS5 strings
quoted_terms = raw_terms.replace('"', '""')
query_text = f'"{quoted_terms}" *'
return (
SearchDoc.objects
.filter(body__table_match=query_text)
.order_by('rank')
)
// 4 - <field>__table_match= hack
@models.Field.register_lookup
class TableMatch(models.Lookup):
lookup_name = 'table_match'
def as_sql(self, compiler, connection):
# attempt to prevent this hack from being a source of unexplainable bugs
if not isinstance(self.lhs, Col):
raise ValueError('choose a column on the model to use __table_match')
# use just the table alias as SQL
lhs, lhs_params = self.lhs.alias, ()
rhs, rhs_params = self.process_rhs(compiler, connection)
params = lhs_params + rhs_params
return "%s = %s" % (lhs, rhs), params
I searched existing forum posts, tickets, and DEPs before posting. I found a few that looked related, but nothing that solved my problem nor seemed like it was moving in that direction. For reference, they are about adding generate_series support (a Postgres-specific table-valued function) and a related ticket (37115) + DEP - sorry for not linking the ticket, but again, I only get 2 links in this post 
. Although it’s using the words I want – table-valued function – the focus of that work seems to be on modeling CompositeField as a precursor. I think my use case is a much narrower thing which doesn’t require any new primitives like that, though I could be wrong.
Thank you!