1

I have a user in my DB with this value:

booking_id -> 25DgW

This field is marked as unique in my model

booking_id = models.CharField(null=False, unique=True, max_length=5, default=set_booking_id)

But now when I query for the user like this:

>>> User.objects.get(booking_id='25dgw')  # I think this should throw a DoesNotExist exacption
<User: John Doe>

Even if I do:

>>> Partner.objects.get(booking_id__exact='25dgw')
<User: John Doe>

I need a query that return the user only if the code is written exactly in the same way than is saved in database: 25DgW instead of 25dgw.

How should I query?

Gocht
  • 9,924
  • 3
  • 42
  • 81
  • Just in case you are not sure, this is the mysql issue. It's not that easy to achieve this using ORM. Raw sql might do it: http://stackoverflow.com/questions/7857669/mysql-case-sensitive-query – Shang Wang Dec 02 '15 at 16:51
  • @ShangWang So what is `__exact` for? Is there any way (hard and not easy) to do this? – Gocht Dec 02 '15 at 16:52
  • 1
    Change your mysql collation to a case sensitive one. `__exact` means that Django should tell the database that you want exact matches. Database collation sets whether the database should consider 'bob' and 'Bob' exact matches. http://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql shows some ways to do this, but it's kind of a big topic. – Peter DeGlopper Dec 02 '15 at 16:55
  • @PeterDeGlopper I have been looking at my schema an I found that it uses `latin1` collation, but `latin1_general_cs` is available, is it possible to change? – Gocht Dec 02 '15 at 17:00
  • I think `__exact` and `=` are the same thing. – Shang Wang Dec 02 '15 at 17:03
  • Yes. See http://stackoverflow.com/questions/5906585/how-to-change-the-default-collation-of-a-database for the sql commands to do this. And, yes, `__exact` is the default, so `filter(foo='bar')` and `filter(foo__exact='bar')` are equivalent. – Peter DeGlopper Dec 02 '15 at 17:03
  • @PeterDeGlopper I did it, now I am using latin1_general_cs collation, ORM is still having the same behavior, Is there something else that I should try? – Gocht Dec 02 '15 at 17:08
  • 1
    Check what happens if you run the select manually - depending on what you changed, it may not have had the desired effect. For instance, I believe changing the database collation changes the default collation for new tables, but does not affect existing tables. – Peter DeGlopper Dec 02 '15 at 17:21
  • @PeterDeGlopper You're right. I have just changed the collation for this table and now it's workig as desired. Thanks dude, please consider give this as an answer and I'll accept it. – Gocht Dec 02 '15 at 17:24

3 Answers3

2

By default, MySQL does not consider the case of the strings. There will be a character set and collation associated with the database/table or column.

The default collation for character set latin1, which is latin1_swedish_ci, which is case-insensitive. You will have to change the collation type to latin1_general_cs which supports case sensitive comparison.

You can to change character-set/collation settings at the database/table/column levels.

The following steps worked for me.

  1. Go to MySQL shell

    mysql -u user -p databasename

  2. Change character set and collation type of the table/column

    ALTER TABLE tablename CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_cs;

  3. Now try the query

    Partner.objects.get(booking_id__exact='25dgw')

This throws an error, table matching query does not exist.

Reference - Character Sets and Collations in MySQL

aprasanth
  • 1,079
  • 7
  • 20
2

User.objects.filter(booking_id='25dgw', booking_id__contains='25dgw').first() seems to work for me (result = None). The booking_id parameter is to assert the correct letters and no more, the booking_id__contains to assert the case sensitiveness.

Jan P
  • 786
  • 6
  • 27
Simoons
  • 81
  • 1
  • 5
0

This seems to work:

qs.filter(booking_id__contains='25DgW').first()
user42488
  • 1,140
  • 14
  • 26