title: How do I use a MySQL user-defined function (UDF) in Django?
body: <p>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.</p>
  
  <p>My UDFs handle a branch of computational chemistry called cheminformatics. 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.</p>
  
  <p>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:</p>
  
  <pre><code>class Node(models.Model):
      title = models.CharField()
      content = models.CharField()
  
  class Structure(models.Model):
      smiles = models.CharField(max_length=255)
      nodes = models.ManyToManyField(Node)
  </code></pre>
  
  <p>Now I want to search for all nodes which have "test" in the title or content, <em>and</em> which match the SMARTS pattern "[C;!H0]" (that is, carbons with at least one hydrogen).</p>
  
  <p>Ideally I would like to write this like the following:</p>
  
  <pre><code>q = ((models.Q(title__icontains="test") |
        models.Q(content__icontains="test")
       ).filter(models.Q(structure__node_smiles = oe_match(smarts="[C;!H0]")))
      )
  </code></pre>
  
  <p>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.</p>
  
  <p>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):</p>
  
  <pre><code>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));
  </code></pre>
  
  <p>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.)</p>
  
  <p>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.</p>