How do I use a MySQL user-defined function (UDF) in Django?

I'm trying to figure out how to construct a Django filter which includes terms which depend on user-defined functions (UDF) defined in MySQL.

My UDFs handle a branch of computational chemistry called cheminformat开发者_运维百科ics. One of the functions (oe_matches) lets me test if a substructure (expressed as a "SMARTS" string) matches a structure (expressed as a "SMILES" string). You can think of these as isomorphic to regular expressions matching a string.

In my database I have a table of topics called "Nodes". This contains searchable text concerning a research topic. Some of the information in the topic concern molecules. I have a table of molecules. Molecules can be in more than one topic, and topics can be about more than one molecule, so I have:

class Node(models.Model):
    title = models.CharField()
    content = models.CharField()

class Structure(models.Model):
    smiles = models.CharField(max_length=255)
    nodes = models.ManyToManyField(Node)

Now I want to search for all nodes which have "test" in the title or content, and which match the SMARTS pattern "[C;!H0]" (that is, carbons with at least one hydrogen).

Ideally I would like to write this like the following:

q = ((models.Q(title__icontains="test") |
      models.Q(content__icontains="test")
     ).filter(models.Q(structure__node_smiles = oe_match(smarts="[C;!H0]")))
    )

where oe_match is something which knows how to generate the right back-end code for MySQL, in this case 'oe_match("[C;!H0]", Structure.smiles)' and where Django sees the structure__node_smiles and does its many-to-many magic to make the parts go together.

I see that I could get what I want with the raw() interface as (modulo minor syntax errors and using the wrong type of join):

SELECT * FROM Node, Node_Structure, Structure WHERE
     ((Node.title LIKE "%test%" OR Node.content LIKE "%test%") AND
      (Node.id = Node_Structure.node AND
       Node_Structure.structure = Structure.id) AND
      oe_matches("[C;!H0]", Structure.smiles));

I would rather not mix and match my database layers that way. Plus, I'll have to do some code restructuring. (Currently I create a filter, which I later pass to the model for the search.)

Longer term, I want others to use my MySQL UDFs for cheminformatics, and I think some set of high-level Django calls for my potential users (other than the two others I work with), would be good, so even if I can solve the immediate problem with raw(), I would still like to know a high-level solution for the long term.

Use QuerySet.extra, which will gladly take your custom function (use the where keyword argument).

http://docs.djangoproject.com/en/1.3/ref/models/querysets/#extra

I think it am extremely late to reply to this as I was in school when this question was asked. We can use the custom(User Defined Function) function by using the Func method provided by django.

from django.db.models import Func

class MultiArgFunc(Func):
    def __init__(self, *expressions):
        super().__init__(*expressions)

class NextShardID(MultiArgFunc):
    function = "next_shard_id"

Now you can use the NextShardID function anywhere you like.