Re: how to call user-defined database functions through the Django query syntax?
On Wed, Apr 20, 2011 at 8:18 AM, Andrew Dalke wrote: > I'm using MySQL and sometimes SQLite as the backend database. Both > databases let me add user-defined functions. I've made a set of UDFs > specific to my problem domain, which is chemsitry. > > How do I call them from a database query? Currently I'm using a raw() > call, which made for some rather ugly SQL construction. > It sounds like you could do this with a QuerySet.extra() call ( http://docs.djangoproject.com/en/1.3/ref/models/querysets/#extra) I don't know how you would use those with Q objects, though. > > Ideally I would like something like > > q = (models.Q(title__icontains="test") & > models.Q(structure_smiles = smartsmatch(smarts="[C;!H0]")) > > -or- > > q = (models.Q(title__icontains="test") & > models.Q(structure_smiles__smartsmatch = "[C;!H0]")) > > > which would get mapped to > >title LIKE 'test' AND structure_id = structure.id AND > oe_matches(structure.smiles, "[C;!H0]") > > You could do something like: myModel.objects.filter(title__icontains="test").extra(where='oe_matches(structure.smiles, "[C;!H0]")') You could encapsulate the extra() call in a function call that annotates a QuerySet that you pass to it, but it's 'outside' of the ORM at that point. -- Regards, Ian Clelland -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: how to call user-defined database functions through the Django query syntax?
Hi Ian, On Apr 20, 5:33 pm, Ian Clelland wrote: > It sounds like you could do this with a QuerySet.extra() call > (http://docs.djangoproject.com/en/1.3/ref/models/querysets/#extra) > > You could do something like: > myModel.objects.filter(title__icontains="test").extra( > where='oe_matches(structure.smiles, > "[C;!H0]")') Interesting. I saw that, but didn't figure out that it might do what I want. I don't think it's possible though, and that's because I didn't explain the complexity. (I thought I could get away with less.) What I have is actually like this: I have a discussion thread with a "question" node and "answer" nodes. Each node can have one or more structures. Each structure can be in one or more node. This is a many-to-many join through an intermediate table. Nodes have "title" and "body." The structure is in the structure table "smiles" column. My search is to find all questions such that: (the word "test" is in the title or body of the question node AND the substructure"[C;!H0]" exists in any of the structures linked to this node) OR (the word "test" is in the title or body of any of its children nodes AND the substructure ".." exists in one of the child node's structures) I did something like this using a lot of LEFT OUTER JOIN, and it seems to work although I think I don't yet have the logic right and this will need more testing ... and review by someone who actually knows SQL. The problem is that I have two ways to get to structure.smiles, one through the parent question and another through the children answers. My left outer join does an alias for one of those. I learned the syntax by seeing what Django does for that case. This means my extra() call would need to know the alias Django uses for making the call, and that's not available programmatically. I could hard code it to "T6" or whatever, but that's very much a hack. (Although it might still be cleaner than my 40 lines of SQL generation code.) Andrew Dalke da...@dalkescientific.com -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: how to call user-defined database functions through the Django query syntax?
Hi Andrew, On Wed, Apr 20, 2011 at 5:18 PM, Andrew Dalke wrote: > I'm using MySQL and sometimes SQLite as the backend database. Both > databases let me add user-defined functions. I've made a set of UDFs > specific to my problem domain, which is chemsitry. > > How do I call them from a database query? I recently put some spare time on the same problem, trying to extend the django database API to support the management of chemical information. My approach to the problem consisted in taking geodjango as a reference extension targeting a specific application domain, I removed the gis-specific features and I then adapted the main skeleton to expose some basic functionalities. This way I was able to map structure searches to calls like Compound.objects.filter(structure__contains='c1cccnc1') or Compound.objects.filter(structure__matches='c1cccac1') I think the result is still very sketchy and experimental, and it might benefit a review by someone with some real understanding of the ORM internals (I only learned some through the process), but if you were interested you can find it here: https://github.com/rvianello/django-chem. Depending on your plans, it would be interesting if we could collaborate on this. Riccardo -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: how to call user-defined database functions through the Django query syntax?
Hi Riccardo, > I recently put some spare time on the same problem, trying to extend > the django database API to support the management of chemical > information. Sweet! I must say that I looked at the code with some dismay. Not because of the code, but because it looks like it's a *lot* of work to handle UDFs. I had hoped it would be much easier. > Depending on your plans, it > would be interesting if we could collaborate on this. A quick look at the implementation tells me it would take me a couple of days to get up to speed and understand how the parts go together. I'm doing this work for a paying client, and I have a solution with raw(). We know already that the raw search doesn't do the right thing for the general case, eg, it doesn't work with our pagination code. So I can see that after we have deployed (which will be next month) and after I go to conferences (ICCE and EuroPython) in June, then it might be something I'll be able to work on, and perhaps even get funding to work on. So yes, I'm interested (though I would implement a comparable OEChem back-end), but no, it's not going to be in the next few months. Cheers! Andrew da...@dalkescientific.com -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: how to call user-defined database functions through the Django query syntax?
Hi Andrew, On Thu, Apr 21, 2011 at 7:59 AM, Andrew Dalke wrote: > I must say that I looked at the code with some dismay. Not because of > the code, but because it looks like it's a *lot* of work to handle > UDFs. I had hoped it would be much easier. I'm sure the code could be simplified and polished (and there are some parts that I know to require some substantial rework).. now that I have a working prototype and I spent some time reading the sources I was just about to start asking some more specific question on the list, but my impression was that if explicit support for this kind of specializations already existed, then django-gis would have used that. >> Depending on your plans, it >> would be interesting if we could collaborate on this. > > [...] > > So yes, I'm interested (though I would implement a comparable OEChem > back-end), but no, it's not going to be in the next few months. Sounds fine, I will also be attending EuroPython next June, so at the latest we might have a chance to talk again about this on that occasion. Cheers, Riccardo -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
