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
