Performing complex lookups using Q objects

Q objects are used to perform complex queries especially when a query involves the AND or OR operations in a WHERE clause. For instance, if we need to do a query similar to the SQL query shown here:

SELECT * FROM Person WHERE name LIKE "Rob%" OR name LIKE "Bob%";

The preceding SQL statement queries for any person whose name either starts with Rob or Bob.

In LIKE "Rob%",ere, the LIKE keyword pattern-matches a string to check whether the string starts with the specified Rob value.

Q objects use the & and | operators for the AND and OR operations when combining the WHERE clauses. The preceding query can be written as follows using Q objects:

Person.objects.get(Q(name__startswith='Rob'), | Q(name__startswith='Bob'))

Exercise 2.17 – performing a complex query using a Q object

Next, using the concept learned before, Q objects let us perform a query for two of the publishers:

1. Import Publisher if you have not already done so and also import Q from django.db.models, as shown in the following command:

   >>> from django.db.models import Q
   >>> from reviews.models import Publisher

2. Execute the Q object query as shown next. Here, we check whether any of the Publisher objects has name starting with either New or Idea:

   >>> Publisher.objects.filter(Q(name__startswith="New")
       | Q(name__startswith="Idea"))
   <QuerySet [<Publisher: New Town Publisher>]>

Since there is no Publisher with name starting with Idea and there was an object with name starting with New, one object was returned.

This is an example Q object query with the AND operator. In this query, we query a Publisher object whose name starts with New and also whose name ends with Publisher:

>>> Publisher.objects.filter(Q(name__startswith="New") &
    Q(name__endswith="Publisher"))
<QuerySet [<Publisher: New Town Publisher>]>

In this case, only one object was returned, which is New Town Publisher, which satisfies the conditions stated in the query. This way, multiple such WHERE clauses can be combined using the AND or OR operators to get the desired results.

Exercise 2.18 – verifying whether a queryset contains a given object

In this exercise, we will use a contains method to check whether the query contains a specified object:

1. Import the Publisher model if you have not already imported it:

   >>> from reviews.models import Publisher

2. Run a query, as shown next. In this example, we are executing a Q object query, and the result has two publishers:

   >>> publishers =
       Publisher.objects.filter(Q(name__startswith="New")
       | Q(name__endswith="Publisher"))
   >>> publishers
   <QuerySet [<Publisher: New Town Publisher>, <Publisher: Katoomba Publisher>]>

3. Fetch a new publisher object and verify whether it is a part of the previous query set:

   >>> new_town_publisher =
       Publisher.objects.get(name='New Town Publisher')
   >>> new_town_publisher
   <Publisher: New Town Publisher>
   >>> publishers.contains(new_town_publisher)
   True

If the object is present, it returns a Boolean value such as True or False.

The following is an example when a certain query set does not have the specified object, in which case the result returned is False:

>>> publishers.contains(Publisher.objects.get(name='Byron
    Bay Press'))
False

In this exercise, we learned how a simple method, contains(), can be used to check whether an object is part of a query set.

Overall, in this entire section, we learned about and used Django’s command-line interactive shell. Using the models created in the previous section for the book review application, we used the Django interactive shell to perform CRUD operations. We also explored various ways to filter and query(Read) from the database. All of these database operations will come in handy while developing any Django application. Using the concepts learned so far in this chapter, in the next section, you will create models for a sample application and perform some of the CRUD operations.