coalesce in underlying sql

[Scott Huang]

Hello,

I'm currently doing some research on whether or not to use querydsl in my project and I have a question about the sql that gets generated, specifically the where clause.  One function of my application basically searches a table for any row that matches the criteria given.  There will always be a date range and the rest of the columns are optional.  Right now I'm using straight jdbc and the query looks like this:

select e.* from event_log e

                 where (person_id = coalesce(:person_id,person_id) or (:person_id is null and person_id is null)) 

                 and event_time >= coalesce(:starting_date,event_time) 

                 and event_time <= coalesce(:ending_date,event_time) 

                 and (agent_channel_id = coalesce(:agent_channel_id,agent_channel_id) or (:agent_channel_id is null and agent_channel_id is null)) 

                 and (agent_user = coalesce(:agent_user,agent_user) or (:agent_user is null and agent_user is null)) 

                 and (channel_id = coalesce(:channel_id,channel_id) or (:channel_id is null and channel_id is null)) 

                 and (classifier = coalesce(:classifier,classifier) or (:classifier is null and classifier is null)) 

                 and (correlation_id = coalesce(:correlation_id,correlation_id) or (:correlation_id is null and correlation_id is null)) 

                 and (device_manufacturer = coalesce(:device_manufacturer,device_manufacturer) or (:device_manufacturer is null and device_manufacturer is null)) 

                 and (device_model = coalesce(:device_model,device_model) or (:device_model is null and device_model is null)) 

                 and (device_os = coalesce(:device_os,device_os) or (:device_os is null and device_os is null)) 

                 and (device_os_version = coalesce(:device_os_version,device_os_version) or (:device_os_version is null and device_os_version is null))

                 and (event_source = coalesce(:event_source,event_source) or (:event_source is null and event_source is null)) 

                 and event_type = coalesce(:event_type,event_type) 

                 and (event_version = coalesce(:event_version,event_version) or (:event_version is null and event_version is null)) 

                 and (message_id = coalesce(:message_id,message_id) or (:message_id is null and message_id is null)) 

                 and (node_id = coalesce(:node_id,node_id) or (:node_id is null and node_id is null)) 

                 and (product_id = coalesce(:product_id,product_id) or (:product_id is null and product_id is null)) 

                 and (source_address_id = coalesce(:source_address_id,source_address_id) or (:source_address_id is null and source_address_id is null)) 

                 and (user_agent_string = coalesce(:user_agent_string,user_agent_string) or (:user_agent_string is null and user_agent_string is null)) 

                 :order_by

I purposely list out every column and use the coalesce function because I want the where clause to be consistent no matter how many columns I want to search by so I can use the same index no matter how many or how few column values are provided.  When looking at the querydsl examples, I'm assuming that if I only want to search by event_time range and channel_id, the query generated would be something like:

select * from event_log where event_time >= ? and event_time <= ? and channel_id = ?

Is my assumption correct?  Is so then I'll have to create a bunch of indexes in different permutations based on the number of columns the user is trying to search by.

 

[timowest]

Using Querydsl should work quite well in your case and the generated query will reflect the where conditions that you specify.

Concerning your coalesce usage I am wondering whether the following might work better:

* require null value

col is null

* require specific non null value

col = :col

instead of the combined

col = coalesce(:col, col) or (:col is null and col is null)