Re: Django builds incorrect sql-query

2014-10-20 Thread Алексей Широков 
Thank you very much Javier!!! 

I've thought about it. I will try. 

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/9645fd3d-8757-4f76-ac5a-a2539926fd04%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Django builds incorrect sql-query

2014-10-20 Thread Javier Guerra Giraldez 
On Mon, Oct 20, 2014 at 3:43 AM, Алексей Широков  wrote:
> Javier, I am unable combine 2 parameters in a single call filter(), because
> It is built on different levels of code.
>
> Is there another solution???


you can build up a dictionary and expand as parameters on a single
filter() call:

   params = {}
   params['sendings__user']=1
   ..
   params['sendings__state']=0b001
   ..
   qs = Document.objects.filter(**params)

i think you can also use a Q() object, but i'm not so sure about the
exact 'simultaneity' behavior.

-- 
Javier

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CAFkDaoTYGTkFNrM1Ncuw3OJQU5V_5pZ8zfAke0mB%3DUhZGvk_%2BQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Django builds incorrect sql-query

2014-10-20 Thread Алексей Широков 
Javier, I am unable combine 2 parameters in a single call filter(), because It 
is built on different levels of code.

Is there another solution???

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/7ca806a0-8373-4333-8c42-2461eaffaedd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Django builds incorrect sql-query

2014-10-20 Thread Javier Guerra Giraldez 
On Mon, Oct 20, 2014 at 1:53 AM, Алексей Широков  wrote:
> SELECT ...
> FROM "document_document"
> INNER JOIN "document_sending" ON ("document_document"."id" =
> "document_sending"."document_id")
> INNER JOIN "document_sending" T4 ON ("document_document"."id" =
> T4."document_id")
> WHERE ("document_sending"."user_id" = 1
>AND T4."state" = 1)
> ORDER BY "document_document"."created" DESC
>
> Why...???
> it is incorrect!!!
>
> must be...
>
> SELECT ...
> FROM "document_document"
> INNER JOIN "document_sending" ON ("document_document"."id" =
> "document_sending"."document_id")
> WHERE ("document_sending"."user_id" = 1
>AND "document_sending"."state" = 1)
> ORDER BY "document_document"."created" DESC


>From the docs [1]:
"To handle both of these situations, Django has a consistent way of
processing filter() and exclude() calls. Everything inside a single
filter() call is applied simultaneously to filter out items matching
all those requirements. Successive filter() calls further restrict the
set of objects, but for multi-valued relations, they apply to any
object linked to the primary model, not necessarily those objects that
were selected by an earlier filter() call."


in other words, your query is equivalent to:

Document.objects.filter(sendings__user=1).filter(sendings__state=0b001)

here, `sendings__user` and `sendings__state` appear on different
`filter()` calls, so each can refer to independent `Sending` objects.

try:

Document.objects.filter(sendings__user=1, sendings__state=0b001)

in this case, `sendings__user` and `sendings__state` appear on the
same `filter()` call, so they refer to `user` and `state` fields of
the same `Sending` object.


[1]: 
https://docs.djangoproject.com/en/1.7/topics/db/queries/#spanning-multi-valued-relationships

-- 
Javier

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CAFkDaoRsD0ZAT4dp2UKu58SH2RC_rYL_s-VW6xV_HRXJTvbyrg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Django builds incorrect sql-query

2014-10-20 Thread Алексей Широков 
Hi,
I have 2 models:


@python_2_unicode_compatible
class Document(models.Model):
uuid = models.CharField(max_length=32, editable=False, unique=True)
contractor = models.ForeignKey(Contractor)
stagcy = models.ForeignKey(StateAgency)
type = models.CharField(max_length=25)
year = models.PositiveSmallIntegerField()
period = models.ForeignKey(Period, null=True, blank=True, 
related_name='+')
created = models.DateTimeField(auto_now_add=True)

class Meta:
unique_together = (
('contractor', 'stagcy', 'type', 'year', 'period'),
)
ordering = ('-created',)

def __str__(self):
return self.uuid


@python_2_unicode_compatible
class Sending(models.Model):
FLAGS = (
'F_CREATED',
'F_QUEUED',
'F_PREPARED',
'F_EXPORTED',
'F_SENT',
'F_CONFIRMED',
'F_COMPLETED',
'F_DELETED',
)

document = models.ForeignKey(Document, related_name='sendings')
uuid = models.CharField(max_length=32, editable=False, unique=True)
korr = models.PositiveSmallIntegerField(null=True, blank=True)
is_external = models.BooleanField(default=False)
user = models.ForeignKey(settings.AUTH_USER_MODEL)
state = bitfield.BitField(FLAGS, default=('F_CREATED',))
detail = jsonfield.JSONField(ensure_ascii=False, editable=False)
created = models.DateTimeField(auto_now_add=True)
queued = models.DateTimeField(null=True)
is_export_allowed = models.BooleanField(default=True)
exported = models.DateTimeField(null=True)
date_sent = models.DateTimeField(null=True)
completed = models.DateTimeField(null=True)
last_modified = models.DateTimeField(auto_now=True)
remark = models.CharField(max_length=1000, blank=True)
is_viewed = models.BooleanField(default=False)

class Meta:
ordering = ('-created',)

def __str__(self):
return self.uuid


Shell

>>> queryset = Document.objects.filter(sendings__user=1)
SELECT ...
FROM "document_document"
INNER JOIN "document_sending" ON ("document_document"."id" = 
"document_sending"."document_id")
WHERE "document_sending"."user_id" = 1
ORDER BY "document_document"."created" DESC 

>>>queryset.filter(sendings__state=0b001)
SELECT ...
FROM "document_document"
INNER JOIN "document_sending" ON ("document_document"."id" = 
"document_sending"."document_id")
INNER JOIN "document_sending" T4 ON ("document_document"."id" = 
T4."document_id")
WHERE ("document_sending"."user_id" = 1
   AND T4."state" = 1)
ORDER BY "document_document"."created" DESC 

Why...??? 
it is incorrect!!!

must be...

SELECT ...
FROM "document_document"
INNER JOIN "document_sending" ON ("document_document"."id" = 
"document_sending"."document_id")
WHERE ("document_sending"."user_id" = 1
   AND "document_sending"."state" = 1)
ORDER BY "document_document"."created" DESC 


How can I solve this problem?

Thanks
Alexi

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/d7659d5e-8495-42e1-a037-056fc71309be%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Incorrect SQL

2014-03-22 Thread Ramiro Morales 
On Sat, Mar 22, 2014 at 7:48 PM, egon.frer...@gmx.de
 wrote:
> With django 1.6.2, python3 and MySQL manage.py inspectdb brings this error:
>
> django.db.utils.ProgrammingError: (1064, "You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL server version for
> the right syntax to use near '%s AND table_schema = DATABASE()\n
> AND character_maximum_length IS' at line 2")
>
> I put some print statements in
> - django/db/backends/mysql/introspection.py line 43
> - django/db/utils.py line 111, 125, 151, 154, 165, 202
> - django/db/backends/util.py line 47
>
> The output with traceback shows in line 22 (from line 43 introspections.py)
> the table_name auth_group
> and then from util line 47 the sql statement in which the table_name  should
> be inserted.
>
> But that doesn't happened. The executed sql statement contains still %s and
> not the table name.
>
> Any idea?

It's weird.

Can you modify the execute() method in
django/db/backends/mysql/base.py to verify the args argument actually
contains  your 'auth_user' table name?

Also, what's the origin of the python-MySQLdb driver you are using?
AFAIK there isn't a 1.2.3 version compatible with Python 3.x. See
https://docs.djangoproject.com/en/1.6/ref/databases/#python-3

-- 
Ramiro Morales
@ramiromorales

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CAO7PdF-RfkDO-feDNZoQNLYhyNSRvDk42zNfkja_9fgJ45y9EQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Incorrect SQL

2014-03-22 Thread egon.frer...@gmx.de 
With django 1.6.2, python3 and MySQL manage.py inspectdb brings this error:

django.db.utils.ProgrammingError: (1064, "You have an error in your SQL 
syntax; check the manual that corresponds to your MySQL server version for 
the right syntax to use near '%s AND table_schema = 
DATABASE()\nAND character_maximum_length IS' at line 2")

I put some print statements in 
- django/db/backends/mysql/introspection.py line 43
- django/db/utils.py line 111, 125, 151, 154, 165, 202
- django/db/backends/util.py line 47

The output with traceback shows in line 22 (from line 43 introspections.py) 
the table_name auth_group
and then from util line 47 the sql statement in which the table_name  
should be inserted.

But that doesn't happened. The executed sql statement contains still %s and 
not the table name.

Any idea?

Egon

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/ccd3b238-cd0b-4c76-9406-9963887aaebf%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
util 151
util 154
util165  {'default': {'ENGINE': 'django.db.backends.mysql', 'PORT': '3306', 
'NAME': 'x', 'HOST': 'xxx', 'PASSWORD': 'xxx', 'USER': 'xxx'}}
utils load_backend 111
client 8
util 202 
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Remove `managed = False` lines for those models you wish to give write DB 
access
# Feel free to rename the models, but don't rename db_table values or field 
names.
#
# Also note: You'll have to insert the output of 'django-admin.py sqlcustom 
[appname]'
# into your database.
from __future__ import unicode_literals

from django.db import models

util 47  SHOW TABLES
class AuthGroup(models.Model):
mysql/introsp. 43  auth_group
util 47  
SELECT column_name, character_maximum_length FROM 
information_schema.columns
WHERE table_name = %s AND table_schema = DATABASE()
AND character_maximum_length IS NOT NULL
Traceback (most recent call last):
  File "/usr/local/lib/python3.3/site-packages/django/db/backends/util.py", 
line 54, in execute
return self.cursor.execute(sql, params)
  File 
"/usr/local/lib/python3.3/site-packages/django/db/backends/mysql/base.py", line 
125, in execute
return self.cursor.execute(query, args)
  File 
"/usr/local/lib/python3.3/site-packages/MySQL_python-1.2.3-py3.3-linux-x86_64.egg/MySQLdb/cursors.py",
 line 184, in execute
self.errorhandler(self, exc, value)
  File 
"/usr/local/lib/python3.3/site-packages/MySQL_python-1.2.3-py3.3-linux-x86_64.egg/MySQLdb/connections.py",
 line 37, in defaulterrorhandler
raise errorvalue
  File 
"/usr/local/lib/python3.3/site-packages/MySQL_python-1.2.3-py3.3-linux-x86_64.egg/MySQLdb/cursors.py",
 line 171, in execute
r = self._query(query)
  File 
"/usr/local/lib/python3.3/site-packages/MySQL_python-1.2.3-py3.3-linux-x86_64.egg/MySQLdb/cursors.py",
 line 330, in _query
rowcount = self._do_query(q)
  File 
"/usr/local/lib/python3.3/site-packages/MySQL_python-1.2.3-py3.3-linux-x86_64.egg/MySQLdb/cursors.py",
 line 294, in _do_query
db.query(q)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL 
syntax; check the manual that corresponds to your MySQL server version for the 
right syntax to use near '%s AND table_schema = DATABASE()\nAND 
character_maximum_length IS' at line 2")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manageMySQL.py", line 10, in 
execute_from_command_line(sys.argv)
  File 
"/usr/local/lib/python3.3/site-packages/django/core/management/__init__.py", 
line 399, in execute_from_command_line
utility.execute()
  File 
"/usr/local/lib/python3.3/site-packages/django/core/management/__init__.py", 
line 392, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.3/site-packages/django/core/management/base.py", 
line 242, in run_from_argv
self.execute(*args, **options.__dict__)
  File "/usr/local/lib/python3.3/site-packages/django/core/management/base.py", 
line 285, in execute
output = self.handle(*args, **options)
  File "/usr/local/lib/python3.3/site-packages/django/core/management/base.py", 
line 415, in handle
return self.handle_noargs(**options)
  File 
"/usr/local/lib/python3.3/site-packages/django/core/management/commands/inspectdb.py",
 line 27, in handle_noargs
for line in

.exclude method generating incorrect sql?

2011-07-17 Thread Jeremy 
I have a query where it looks like Django 1.3 is generating incorrect
sql. It involves these four models.

class Aaa(models.Model):
pass

class Bbb(models.Model):
aaa = models.ForeignKey(Aaa, related_name='bbbs')

class Ccc(models.Model):
bbb = models.ForeignKey(Bbb, related_name='cccs')

class Ddd(models.Model):
aaa = models.ForeignKey(Aaa, related_name='ddds')
passed = models.BooleanField()

So here's the query; I expect it to give me the number of cccs that
are not attached to a non-passed ddd.
I [1]: Ccc.objects.exclude(bbb__aaa__ddds__passed=False).count()

Here's the sql generated by django, from connection.queries:
SELECT COUNT(*)
FROM "myapp_ccc"
INNER JOIN "myapp_bbb" ON ("myapp_ccc"."bbb_id" = "myapp_bbb"."id")
WHERE NOT (("myapp_bbb"."aaa_id" IN (SELECT U1."id"
FROM
"myapp_bbb" U1
INNER
JOIN "myapp_aaa" U2 ON (U1."aaa_id" = U2."id")
INNER
JOIN "myapp_ddd" U3 ON (U2."id" = U3."aaa_id")
WHERE
U3."passed" = false ) AND "myapp_bbb"."aaa_id" IS NOT NULL))

It looks like the inner select statement returns a list of bbb ID's,
but the WHERE NOT condition asks if a aaa id is in the list of bbb
ID's, which doesn't make any sense. Basically, it should say:
WHERE NOT (("myapp_bbb"."id" IN (SELECT U1."id"

What do you think? Is this a bug?

-- 
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.

aggregate produces incorrect sql statement

2011-03-16 Thread zeroos 
Hi folks!

I have following model:

class Solution(models.Model):
griddler = models.ForeignKey(Griddler)
user = models.ForeignKey(User)
user_time = models.IntegerField(null=True)
date = models.DateTimeField(auto_now_add=True)

class Griddler(models.Model):

solved_by = models.ManyToManyField(Uesr, through='Solution')


What I am trying to do is to find the Griddler that has the biggest
avg user time. I've written the following query:
Solution.objects.values('griddler').annotate(a=Avg('user_time')).aggregate(Max('a'))

Unfortunately, after executing it I get DatabaseError: (1064, "You
have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'FROM
(SELECT `griddlers_solution`.`griddler_id` AS `griddler_id`,
AVG(`griddlers' at line 1")

>>> connection.queries[-1]
{'time': '0.000', 'sql': u'SELECT  FROM (SELECT
`griddlers_solution`.`griddler_id` AS `griddler_id`,
AVG(`griddlers_solution`.`user_time`) AS `a` FROM `griddlers_solution`
GROUP BY `griddlers_solution`.`griddler_id`,
`griddlers_solution`.`griddler_id` ORDER BY NULL) subquery'}



The problem is that Max('a') is skipped in the sql query. It should
look like this: SELECT Max(`a`) FROM...

I am using Django from SVN. >>> django.get_version()
'1.3 beta 1'

and MySQL backend.

Is it a Django bug that I should report or am I missing something?

-- 
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.

Query with extra select + order_by generates incorrect SQL

2009-07-13 Thread yatish 

I'm constructing a query with an extra select subquery that also uses
an order_by and it seems to generate SQL that MySQL doesn't like since
it's generating a GROUP BY with missing parens.

The problem is similar to this doing this:

Blog.objects.extra(select={
'entry_count': 'SELECT COUNT(*) FROM blog_entry WHERE
blog_entry.blog_id = blog_blog.id'
},).annotate(Count('something_else', distinct = True).order_by
('entry_count')

The resulting SQL puts the whole subquery in the GROUP BY section, but
doesn't put ()'s around it so MySQL doesn't accept it.

I am able to "fix" this problem by modifying BaseQuery.get_grouping()
to simply insert ()'s around the extra_selects items, but perhaps I'm
making a more fundamental mistake in creating the query?

Thanks,

yatish

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---