[Question] how to set dynamic query in where clause

[hugehoo]

Hello,

I'm currently working with the sqlx and have encountered a scenario where I need to conditionally include the column event_name filter in my SQL query. Specifically, I want to omit the event_name condition when its corresponding value is nil or not provided, without manually constructing the entire query string.

Here's the query and code I'm using:

	query := `
		SELECT *
		FROM i_event_log_kst_day e
		WHERE e.partition_timestamp BETWEEN DATE :startTime AND DATE :endTime
		AND e.partner_id = :partnerID
		AND e.event_name = :eventName
		ORDER BY e.created_at, e.user_id
	`
	arg := map[string]interface{}{
		"startTime": startTime,
		"endTime":   endTime,
		"partnerID": partnerID,
		"eventName": eventName,
	}
	
        result, err := a.db.NamedQuery(query, arg);

When eventName is nil or not provided, I would like the query to exclude the AND e.event_name = :eventName condition, effectively ignoring the event_name column in the filtering process.

Is there a recommended approach within the sqlx library to achieve this dynamic behavior without resorting to manual query string manipulation?

Thank you for your assistance.

[noxterr]

Hey!
I found this an interesting thing to bring up since I am dealing with a similar thing.
However, my temporary solution to this has been working on the SQL itself with IFNULL in order to "bypass" the field if it was null.

For your query, it would be something like:

query := `
	SELECT *
	FROM i_event_log_kst_day e
	WHERE e.partition_timestamp BETWEEN DATE :startTime AND DATE :endTime
	AND e.partner_id = :partnerID
	AND IFNULL(:eventName, e.event_name) = e.event_name
	ORDER BY e.created_at, e.user_id
`

May look like a "cheat", but I found this to be working on my hand.

Hope this can help!