Hello,
I have a RAW Database query that is connecting 2 tables which Inner Queries and the Query is as below.
SELECT * FROM vehicles WHERE ID NOT IN (SELECT vehicles_id from bookingDetails WHERE (sdt BETWEEN start_date AND end_date) OR (edt BETWEEN start_date AND end_date)
here, vehicles, and bookingDetails are two table defined different app, and sdt & edt is the date input variable, when I am starting the server there is no error, but when I search for the Car, and redirect to next page, and I am retrieving the data in HTML through loop that time I am getting an error which is as below.
I have tried to write RAW Queries as below:
with connection.cursor() as cursor: cursor.execute('''SELECT * FROM vehicles WHERE ID NOT IN (SELECT vehicles_id from bookingDetails WHERE (sdt BETWEEN start_date AND end_date) OR (edt BETWEEN start_date AND end_date))''') row = cursor.fetchall()
and
row = bookingDetails.objects.raw('''SELECT * FROM vehicles WHERE ID NOT IN (SELECT vehicles_id from bookingDetails WHERE (sdt BETWEEN start_date AND end_date) OR (edt BETWEEN start_date AND end_date))''')
any-one can please help me to solve this error, please.
Kind Regards,
Dhaval
The message is fairly straight forward - you’re referencing a table named “vehicles” that doesn’t exist. You mention that vehicles is a table defined elsewhere, but you don’t really specify where or how.
If vehicles is a table defined in your project in a model, then be aware that the actual table is constructed by the name of the Django app followed by the model name. For example, if you have an app named my_app and a table named data, the actual table name would be something like my_app_data.
So what you probably want to do is look at your database to see what the table is named, and work from there.
(There are other possibilities, but this seems the most likely. If this isn’t it, we’re going to need a lot more information about your database and these tables.)
I’ve imported all the models defined in another app in the same main project. I also tried by doing clientDashboard_vehicles, even though I was getting the same error message that Relation “vehicles” that doesn’t exist
here how my models.py looks like.
I would look directly at the database to see what tables exist. (Obviously, the tools and/or commands to do this depends upon what database engine you’re using.)
also, are you using migrations to manage these tables? If so, have you done a makemigrations / migrate since creating these models?
Yes, I’ve done makemigrations and migrate to create the tables.
here it is how my database looks like
So looking at things a little more closely, in addition to specifying the wrong table, you’re not handling your parameters correctly in your query.
The cursor.execute call is passing that string directly to the database, but the database doesn’t know what sdt or edt are - they’re Django variables. See the example in the Executing custom SQL directly section of the docs, along with the parameter-passing section of the Python DB API docs to see how to pass sdt and edt as parameters in the query.
Another issue you have is that in your second attempt, you’re querying the vehicles table (still needing to correct the name) but you’re trying to execute it in the context of bookingDetails.objects.raw. This would need to be vehicles.objects.raw since you’re issuing a select statement on the vehicles table. (Not entirely precisely accurate - the documentation does explain the situations where you can query a different table than the one on which you’re executing the query - but this isn’t one of those exceptions.)
So as it stands, if you correct the table name without correcting the parameter errors, you’ll still get an error, but it should be a different error.
Also, I’ll mention that a raw query is not necessary for what you’re trying to do. Unless there’s some other reason for doing it this way, this query can be expressed in the Django ORM.
but I don’t know how I can write above RAW Query in Django ORM, that’s why I was performing RAW Query
I also checked the documentation for that, but still having confusion for how to write ORM Query for the above RAW Query
So it looks like you’re trying to select vehicles that aren’t in bookingDetails where the start_date and end_date contain either the sdt or edt. (Note - your condition doesn’t account for the situation where the sdt is before start_date and edt is after the end date - assuming that what you’re trying to do is find vehicles that are available for the range of dates between sdt and edt)
So what you have written would be something like
vehicles.objects.exclude(bdvechicles__start_date__lt=sdt, bdvechicles__end_date__gt=sdt
).exclude(bdvechicles__start_date__lt=edt, bdvechicles__end_date__gt=edt)
(There are other ways to code this, but the first exclude should filter out all the rows where there is a related bookingDetails instance with the start_date and end_date bracketing sdt, and the second exclude should filter out all the corresponding cases with edt.)
for your given ORM Query, I got the following error
[‘“10/04/2021” value has an invalid date format. It must be in YYYY-MM-DD format.’]
That’s just a data formatting error - you will need to ensure that all your data is in the proper format regardless of what method you use.
Also, if you’re really looking for non-overlapping ranges, take a look at Range overlap in two compares | Ned Batchelder, and the article it reference at Determining if Two Date Ranges Overlap - Soliant Consulting
I didn’t get you for Non-Overlapping ranges, also how to check the correct date formatting? I am asking this much because I am beginner
also, this Data Format error is from PostgreSQL Database or from Django?
The date formatting issue covers a lot of ground - some of it dealing with how your form is created where they’re entering a date, and some of it dealing with the different ways that Django / Python can represent dates internally. (It’s way too large a topic for me to cover in a forum post.)
The DateField docs on the forms page is a place to start, but it’s going end up sending you through a lot of the documentation.
You ought to review everything in the Django docs on DateFields, both how they work in the models and how you accept them as input from forms. It may also be worth your while to review the Python datetime module as well.
You might also want to search for some blog posts or other research on date handling - it’s not a trivial topic and has many areas for causing problems - localization being just one of them.
DATETIME_FORMAT = 'l, d s F Y, P T' → this is the format in Django
format: 'dd/mm/yyyy' → this is the format I have changed using MaterializeCSS,
and PostgreSQL Database format is YYYY-MM-DD
also, I am not using Django Forms
I’ve solved the date format issue.
vehicles.objects.exclude(bdvechicles__start_date__lt=sdt, bdvechicles__end_date__gt=sdt
).exclude(bdvechicles__start_date__lt=edt, bdvechicles__end_date__gt=edt)
Sir, this above ORM Query will do the same thing as I am trying to do it via RAW Query, like displaying the cars excluding those cars who are booked on given date input?
@KenWhitesell , please sir
