django select records before given date

Question

I have two models,

Item and Transaction

class Item(models.Model):
    id = models.AutoField(db_column='id', primary_key=True)
    name = models.CharField(max_length=48, verbose_name=_(u'Name')) 

class Transaction(models.Model):
    transactionItem = models.ForeignKey(Item, verbose_name=_(u'Transaction Item'))
    createdDate = models.DateField(default=datetime.date.today(), verbose_name=_(u'Date'))

So in db, Transaction table will contain transactions for Item.

I need to select Items which have records in transaction table before given date. If a Item has transactions before and after the given date, I need to ignore it.

EDIT: Sample Data Item Table

--------------------------
| id       | name        |
--------------------------
| 1277     | Diode       |
--------------------------
| 1278     | Resistor    |
--------------------------

Transaction Table

---------------------------------------------
| id       | transactionItem  | createdDate        |
---------------------------------------------
| 1        | 1277             | 2014-01-14  |
---------------------------------------------
| 2        | 1277             | 2014-02-09  |
---------------------------------------------
| 3        | 1278             | 2014-01-08  |
---------------------------------------------

If given date is 2014-01-13, I should only get record with id = 3 from transaction table.

How to filter the records in django? thanks.

Don't understand your question: *"I need to select Items which ha record in transaction table only **before** given date. If a Item has transactions **before** and after the given date, I need to ignore it."* Can you post sample data? — dani herrera, Dec 09 '14 at 15:17
I edited my question. Basically i need to select all records from transaction tbl before a given date. this is to list items which are not moving since the given date. — user3575232, Dec 09 '14 at 15:23
Does this answer your question? [How do I filter query objects by date range in Django?](https://stackoverflow.com/questions/4668619/how-do-i-filter-query-objects-by-date-range-in-django) — Shmack, Nov 03 '21 at 06:38

score -1 · Answer 1 · answered Dec 09 '14 at 22:30

I tried using annotate, but it still gives me transaction which had both dates less than and greater than given date. However I get the correct results using below query,

idle_ids = ( Transaction
        .objects
        .filter( date__lte = some_date )
        .values_list('transactionItem__id', flat=True)
        .distinct()
        .order_by()
        )

idle_ids2 = ( Transaction
        .objects
        .filter( date__gte = some_date ).filter(transactionItem_id__in=idle_ids)
        .values_list('transactionItem__id', flat=True)
        .distinct()
        .order_by()
        )

items = Item
     .objects
     .filter(id__in=idle_ids).exclude(id__in=idle_ids2)

django select records before given date

1 Answers1