How do I execute a query with tuple comparison?

[vamshiaruru-virgodesigns]

Hi all, I am trying to implement the following query:

SELECT push_notification_message.delivered_at AS "push_notification_message.delivered_at"
FROM public.push_notification_message
WHERE (template_id, recipient_id) IN ((17, 37));

And I can't figure out how to do this. Here are the things I tried out so far:

	in_query_string := strings.Join(utils.Map(
		input,
		func(ele PnDeliveredAtInputEle, _ int) string {
			return fmt.Sprintf("(%d, %d)", ele.TemplateId, ele.RecipientId)
		},
	), ",")

	query := psql.RawStatement(`
		SELECT push_notification_message.receipient_id
		FROM push_notification_message
		WHERE (push_notification_message.template_id, push_notification_message.receipient_id) IN (#1);
	`, psql.RawArgs{"#1": in_query_string})

This generate the query

SELECT push_notification_message.delivered_at AS "push_notification_message.delivered_at"
FROM public.push_notification_message
WHERE (template_id, recipient_id) IN ('(17, 37)');

Note the extra string characters inside the array, which breaks things.

Any help is appreciated, thanks!

[houten11]

Well, you passed a string as a parameter, and that's where the quote came from.
If you want to dynamically modify raw queries, you can't do that by modifying query parameters. You'll need to modify the SQL query string.
This line:

WHERE (push_notification_message.template_id, push_notification_message.receipient_id) IN ((#1, #2), (#3, #4));

And add as many (#f, #s) pairs as needed.

This is tedious and error-prone work and dynamic modifying sql string is best to be done with SQL builder.
For instance:

pairs := []Expression{}

// some custom logic to dynamically specify template_id, receipient_id
pairs = append(pairs, ROW(Int(1), Int(33))
pairs = append(pairs, ROW(Int(1), Int(33))

stmt := SELECT(
    PushNotificationMessage.DeliveredAt,
).FROM(
    PushNotificationMessage,
).WHERE(
    ROW(PushNotificationMessage.TemplateID, PushNotificationMessage.ReceipientID).IN(pairs...),
)

[vamshiaruru-virgodesigns]

Thank you, I didn't about the ROW construct!