Hi all,
I have these questions regarding some sql queries which I was able to
do in msacess
but i wasnot with django which is using mysql database...sorry for the
length mail, but i am just trying to make the question as clear as
possible...
I have a table structure something like this: which basically is an
offer table that has a foreign key to category table and this offer
can belong to many sellers (offerseller model) and the different
sellers can have several prices for the same offer (for example, buy 2
for 10USD, 3 for 14USD, 4 for 17 USD, etc..) and this is stored in
the offerprice table.
=============================================
model:
class Category(models.Model):
name = models.CharField(maxlength=50,)
description = models.TextField(maxlength=250)
class Seller(models.Model):
shop_name = models.CharField(maxlength=50)
phone_number1 = models.CharField(maxlength=15)
phone_number2 = models.CharField(maxlength=15)
VAT_number=models.CharField(maxlength=10)
class Offer(models.Model):
name = models.CharField(maxlength=50)
category = models.ForeignKey(Category)
description = models.TextField(maxlength=250)
class OfferSeller(models.Model):
offer = models.ForeignKey(Offer)
seller = models.ForeignKey(Seller)
original_price = models.DecimalField(max_digits=11,
decimal_places=2)
from_date = models.DateField(null=False, blank=False)
to_date = models.DateField(null=False, blank=False)
active = models.BooleanField(null=False, blank=False, default=1)
class OfferPrice(models.Model):
offer_seller = models.ForeignKey(OfferSeller)
amount = models.PositiveIntegerField(null=False, blank=False,
unique=False, default=1)
price = models.DecimalField(max_digits=11, decimal_places=2)
=========================================
I created a simple query like this to join the offer, offerseller and
offerprice tables to get
all the info I need about an offer, i.e the offer, the seller and the
price
SELECT offers_offer.id, offers_offerseller.original_price as
original_price, offers_offerprice.price as price
FROM (offers_offer INNER JOIN offers_offerseller ON offers_offer.id =
offers_offerseller.offer_id)
INNER JOIN offers_offerprice ON offers_offerseller.id =
offers_offerprice.offerseller_id
but I get error that the offers_offerprice.offerseller_id column
doesnt exist.
The same query excutes without a problem in microsoft access.
any idea why it is not working?
Actually, I was trying to get this complex query to work, but the
problem I mentioned above gets me stuck... so any sugge
================================================================
what I wanted to do was something like...
I wanted to get the top N categories with the most offers, and from
this top
categories I wanted to get the top M offers
I get the top categories simply by:
query = "SELECT category_id FROM offers_offer GROUP BY category_id
order by count(*) DESC"
cursor = connection.cursor()
cursor.execute(query)
categories_l= [row[0] for row in cursor.fetchall()[:N]]
and I wanted to use this list of categories to get the offers that I
am interested in so I did
something like this (I know this is very inefficient, I was just
trying to see if it works, and it works in microsoft access):
query = """SELECT id, original_price, price FROM \
(SELECT offers_offer.id,
offers_offerseller.original_price as original_price, \
offers_offerprice.price as price\
FROM (offers_offer INNER JOIN offers_offerseller ON
offers_offer.id = offers_offerseller.offer_id) \
INNER JOIN offers_offerprice ON offers_offerseller.id =
offers_offerprice.offerseller_id \
) AS f1\
WHERE f1.category_id IN (%s)""" % ','.join(['%s'] *
len(categories_l)) \
query += """ AND (SELECT COUNT(*) FROM \
(SELECT offers_offer.id as id,
offers_offer.category_id as category_id,
offers_offerseller.original_price as original_price,
offers_offerprice.price as price \
FROM (offers_offer INNER JOIN offers_offerseller ON
offers_offer.id = offers_offerseller.offer_id) \
INNER JOIN offers_offerprice ON offers_offerseller.id
= offers_offerprice.offerseller_id \
) AS f2 \
WHERE f2.category_id = f1.category_id \
AND (f2.original_price-f2.price)/
(f2.original_price)>(f1.original_price-f1.price)/(f1.original_price) )
< %s \
ORDER BY (f1.original_price-f1.price)/
(f1.original_price*1.0) DESC"""
par = categories_l + [n_prod]
cursor = connection.cursor()
cursor.execute(query, par)
offers_list = cursor.fetchall()
==========================================================
considering the join problem is solved somehow, any suggestions to do
this in a more effecient way?
thanks a lot!
Oumer
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
