Raw SQL Query using a list parameter fails

Question

I have the following code:

 products = Product.objects.raw(
            'SELECT DISTINCT ON(I.product_id) P.id, P.name, P.desc, C.name AS ram, I.image '
            'FROM products_product AS P '
            'LEFT JOIN categories AS RPC ON P.id = RPC.product_id '
            'LEFT JOIN company AS CP ON P.company_id = CP.id '
            'LEFT JOIN category AS C ON RPC.category_id = C.id '
            'LEFT JOIN image AS I ON I.product_id = P.id '
            'WHERE P.id IN %s', list
    )

I receive the following error:

not all arguments converted during string formatting

Instead of list I tried to use [2,4] or ['2', '4'] , same error.

If I use without a parameter is working. I use PostgreSQL.

Answer 1

According to SQL notation for IN() this could be IN ('2', '4'). So you could try something like:

~'WHERE P.id IN {0}'.format(tuple(your_list))~ << don't.

EDIT:

Warning

Do not use string formatting on raw queries or quote placeholders in your SQL strings!

Following Django documentation about it, you can use:

products = Product.objects.raw(
    'SELECT DISTINCT ON(I.product_id) P.id, P.name, P.desc, C.name AS ram, I.image '
    'FROM products_product AS P '
    'LEFT JOIN categories AS RPC ON P.id = RPC.product_id '
    'LEFT JOIN company AS CP ON P.company_id = CP.id '
    'LEFT JOIN category AS C ON RPC.category_id = C.id '
    'LEFT JOIN image AS I ON I.product_id = P.id '
    'WHERE P.id IN %s', params=[your_list])