Search in a ManyToManyField in Admin interface

[João Olavo Baião de Vasconcelos]

Hi all,

I'd like to search in a m2mfield using the search_fields trick in admin interface (django 1.0.2).

A simple example of models:
"""
class Book(models.Model):
  title = models.CharField(...)
  authors = models.ManyToManyField('author')

class Author(models.Model):
  name = models.CharField(...)
"""

In admin.py, would be great if it could be done simply like this:
"""
class BookAdmin(admin.ModelAdmin):
    search_fields = ['title', 'authors']
admin.site.register(models.Book, BookAdmin)
"""

Then, in the search box of the Book page, I could just type an author and search through the m2m field 'authors'. But, doing this way, I got the following error:

Related Field has invalid lookup: icontains

I could achieve this search going to the link http://localhost/admin/bookstore/book/?authors__name=john , so I still think this is possible.

Another idea could be creating a method in the model that returns a list of authors:
"""
class Book(models.Model):
  title = models.CharField(...)
  authors = models.ManyToManyField('author')
  def get_authors(self):
    return authors.all()

class Author(models.Model):
  name = models.CharField(...)
"""
admin.py:
"""
class BookAdmin(admin.ModelAdmin):
    search_fields = ['title', 'authors', 'get_authors']
admin.site.register(models.Book, BookAdmin)
"""
But then I got the following error message:

Cannot resolve keyword 'get_authors' into field. Choices are: title, authors

So... how can I do this?

Thanks!!
--
João Olavo Baião de Vasconcelos
Bacharel em Ciência da Computação
Analista de Sistemas - Infraestrutura
joaoolavo.wordpress.com

[Karen Tracey]

On Wed, Jan 28, 2009 at 6:19 AM, João Olavo Baião de Vasconcelos <joao...@gmail.com> wrote:

Hi all,

I'd like to search in a m2mfield using the search_fields trick in admin interface (django 1.0.2).

A simple example of models:
"""
class Book(models.Model):
  title = models.CharField(...)
  authors = models.ManyToManyField('author')

class Author(models.Model):
  name = models.CharField(...)
"""

In admin.py, would be great if it could be done simply like this:
"""
class BookAdmin(admin.ModelAdmin):
    search_fields = ['title', 'authors']
admin.site.register(models.Book, BookAdmin)
"""

Then, in the search box of the Book page, I could just type an author and search through the m2m field 'authors'. But, doing this way, I got the following error:

Related Field has invalid lookup: icontains

I could achieve this search going to the link http://localhost/admin/bookstore/book/?authors__name=john , so I still think this is possible.

You are including the __name in your manual search but you left it out of the search_fields you listed in your ModelAdmin.  So what happens if you make search_fields = ['title', 'authors__name']? 

Karen

[João Olavo Baião de Vasconcelos]

On Wed, Jan 28, 2009 at 1:31 PM, Karen Tracey <kmtr...@gmail.com> wrote:

You are including the __name in your manual search but you left it out of the search_fields you listed in your ModelAdmin.  So what happens if you make search_fields = ['title', 'authors__name']? 

I did it, and got this error message:
DatabaseError: ORA-00932: inconsistent datatypes: expected - got CLOB

As it's an Oracle error, I tried with a sqlite database and it worked just fine!

But, as I wanna continue using oracle, how can I solve it? Is this a bug?

Using CentOS 5.2, Django 1.0.2, cx_Oracle 4.4.1_10g, Oracle 9i and oracle-instantclient-basic-10.2.0.4-1

Thanks for the reply, Karen.

--

João Olavo Baião de Vasconcelos

[Karen Tracey]

On Wed, Jan 28, 2009 at 11:46 AM, João Olavo Baião de Vasconcelos <joao...@gmail.com> wrote:

On Wed, Jan 28, 2009 at 1:31 PM, Karen Tracey <kmtr...@gmail.com> wrote:

You are including the __name in your manual search but you left it out of the search_fields you listed in your ModelAdmin.  So what happens if you make search_fields = ['title', 'authors__name']? 

I did it, and got this error message:
DatabaseError: ORA-00932: inconsistent datatypes: expected - got CLOB

As it's an Oracle error, I tried with a sqlite database and it worked just fine!

But, as I wanna continue using oracle, how can I solve it? Is this a bug?

Using CentOS 5.2, Django 1.0.2, cx_Oracle 4.4.1_10g, Oracle 9i and oracle-instantclient-basic-10.2.0.4-1

Thanks for the reply, Karen.

Are you absolutely sure you were using the exact same code for the working sqlite test as the failing Oracle one?  I cannot recreate any problem using your sample models (fixed up to include the missing bits like max_length and correcting case of 'authors' in the ManyToMany) on my Oracle setup.  I'm using Oracle 10g Express on Windows, cx_Oracle 4.4, Django 1.0 (and I don't know what that instantclient thing is so I'm not sure I have any corresponding thing on Windows).

(The fact that you can get the query to work when you fixup the search URL manually implies to me that your Oracle setup can process the correct query properly, and that somehow what you specified in search_fields is resulting in a slightly different query than expected.)

Karen

[Ian]

On Jan 28, 9:46 am, João Olavo Baião de Vasconcelos
<joaool...@gmail.com> wrote:

> On Wed, Jan 28, 2009 at 1:31 PM, Karen Tracey <kmtra...@gmail.com> wrote:
> > You are including the __name in your manual search but you left it out of
> > the search_fields you listed in your ModelAdmin.  So what happens if you
> > make search_fields = ['title', 'authors__name']?
>
> I did it, and got this error message:

> *DatabaseError: ORA-00932: inconsistent datatypes: expected - got CLOB*

>
> As it's an Oracle error, I tried with a sqlite database and it worked just
> fine!
>
> But, as I wanna continue using oracle, how can I solve it? Is this a bug?

What are the Oracle types of the columns associated with the title and
name fields? Both are declared as CharFields, for which Django
normally uses a VARCHAR2 column. If either is actually a CLOB column
for some reason (perhaps you changed the model but didn't drop and
recreate the table), you would get that error when you tried to search
over it. To fix it, either replace the offending CharField with a
TextField, or change the offending CLOB to a VARCHAR2.

Hope that helps,
Ian

[João Olavo Baião de Vasconcelos]

I think that I realised what is the problem.

Karen, just add a TextField to the Book model (say, "description = models.TextField()"), try to search for something and you'll see that error message. I'm not 100% sure about it, coz I tested this at work and then came home.

Probably is the same bug reported here: http://code.djangoproject.com/ticket/4186

I think that you'll have to drop the hole userspace and run ryncdb again after adding the 'description' attribute, right?

On Wed, Jan 28, 2009 at 3:49 PM, Karen Tracey <kmtr...@gmail.com> wrote:

I cannot recreate any problem using your sample models

Well, to be honest, this book example was just to illustrate what I want, but actually I was using another class name and class attributes. So I tried using this book example and it also worked here!

 

  I'm using Oracle 10g Express on Windows, cx_Oracle 4.4, Django 1.0 (and I don't know what that instantclient thing is so I'm not sure I have any corresponding thing on Windows).

instantclient is the Oracle Client, which brings the lib that helps to connects to oracle server (libclntsh.so).

Thanks!!
--

João Olavo Baião de Vasconcelos

[Ian]

On Jan 28, 1:25 pm, João Olavo Baião de Vasconcelos

<joaool...@gmail.com> wrote:
> I think that I realised what is the problem.
>
> Karen, just add a TextField to the Book model (say, "description =
> models.TextField()"), try to search for something and you'll see that error
> message. I'm not 100% sure about it, coz I tested this at work and then came
> home.
>
> Probably is the same bug reported here:http://code.djangoproject.com/ticket/4186

Yes, that's the problem. The admin automatically adds .distinct() to
the queryset whenever one of the search fields is in a related model,
which would trigger the error. If you drop the `author__name` field
and just search over 'title', I think you'll find that it works.

This is a limitation of Oracle, and there's not a whole lot we can do
about it, other than to suggest you not structure your models that
way. Probably your best solution will be to put the TextField in a
separate one-to-one related model. If you do that, and you avoid
using the `list_select_related` admin option, and you exclude the
related model from the `list_display` admin option, then you should be
okay.

Regards,
Ian

[João Olavo Baião de Vasconcelos]

On Wed, Jan 28, 2009 at 7:36 PM, Ian <ian.g...@gmail.com> wrote:

Yes, that's the problem.  The admin automatically adds .distinct() to  the queryset whenever one of the search fields is in a related model,  which would trigger the error.

Ok, got it.
 

This is a limitation of Oracle, and there's not a whole lot we can do  about it, other than to suggest you not structure your models that way.

But in my case, it gotta have a TextField.
 

 Probably your best solution will be to put the TextField in a separate one-to-one related model.

I tried it, but it became awful. For instance, every time that I add a Book, not only there's a list of all other descriptions already added, but also I've to add it in a separated window. I'm more inclined to change to a mysql db.

Thanks, Ian!
--

João Olavo Baião de Vasconcelos