On Thu, Dec 22, 2011 at 12:27 PM, kr0na <enarbu...@gmail.com> wrote:
> Im trying to compare two tables in a second database that is a
> migrated database from mssql. The second database doesn't have any
> apps for it, it will only be used for queries.
>
> This are my models for the two tables.
> from django.db import models
>
> class invGroups(models.Model):
> groupID = models.SmallIntegerField(primary_key = True)
> categoryID = models.SmallIntegerField()
>
> def __unicode__(self):
> return self.item
>
> class Meta:
> db_table = 'invGroups'
>
> class invTypes(models.Model):
> typeID = models.SmallIntegerField(primary_key = True)
> typeName = models.CharField(max_length = 200)
> published = models.SmallIntegerField()
> groupID = models.SmallIntegerField()
>
> def __unicode__(self):
> return self.item
>
> class Meta:
> db_table = 'invTypes'
>
> And the query so far.
> item_search_results = invTypes.objects.using(
> 'DataDump'
> ).filter(
> typeName__icontains = search_query
> )[0:15]
>
> I currently can select from only one database, and the query is what i
> have so far. I tried to use ForeignKey with no results.
I didn't really understand what you are asking here. Where does the
second database come into it?
> Can I do this
> without using a RAW query? Im trying to achieve this query:
> SELECT
> typeName
> FROM
> invGroups,
> invTypes
> WHERE
> invTypes.groupID = invGroups.groupID and
> invGroups.categoryID in (7, 8, 9, 18, 20) and
> invTypes.typeName like 'query%'
> ORDER BY
> invTypes.typeName;
>
First off, currently your objects are not related to each other. You
need to specify a foreign key relationship between the two related
models:
class invTypes(models.Model):
typeID = models.SmallIntegerField(primary_key = True)
typeName = models.CharField(max_length = 200)
published = models.SmallIntegerField()
group = models.ForeignKey('invGroups', db_column='groupID')
Then, you can simply use Django's ORM to query the tables:
invType.objects.filter(invgroups__categoryid__in[7,8,9,8,20],
typename__ilike=query).values_list('typename', flat=True)
Django will do an explicit join, rather than the implicit one in your query.
There are a few other things I find strange with your code:
Readability: your class names should be UpperCamelCase and your
variable, function and method names either lowerCamelCase or
lower_case_with_underscores. PEP 8 (and me) prefer the latter.
Straight up bugs: Both model's unicode methods refer to self.item, but
there is no self.item.
Specifying the type and name of your primary key. Django will auto add
a primary key with a sane type if you don't. You've chosen
SmallIntegerField - in most databases, this will have a maximum value
of 32,768, meaning you can put that many items in your DB table and no
more.
It could be you are mapping Django onto an existing DB structure,
which would explain the strange model attribute names. When doing
this, it's easier to have a sensible python name for the attribute,
and specify the actual column name in the db_column argument, as I did
adding the ForeignKey.
Cheers
Tom
--
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.
