1

First of all, if someone has a better sentence for my question, feel free to comment.

I want to translate this query into Golang

SELECT
    mou."id",
    mou."name",
    mou.description,
    mou.img_url,
    um.favorite 
FROM
    majors_of_universities mou
    JOIN field_of_studies fos ON mou.field_of_studies_id = fos."id"
    JOIN univ_major um ON mou."id" = um.majors_of_universities_id 
WHERE
    mou."name" ILIKE '%%' 
    AND fos."name" IN ( 'IT & Software', 'Analisis Data & Statistik' ) 
ORDER BY
    mou."name" 
    LIMIT 99 OFFSET 0;

This query works well, btw. I'm using sqlc as a generator and by it rules (CMIIW), I changed...

  • '%%' to $1
  • 'IT & Software', 'Analisis Data & Statistik' to $2
  • 99 to $3
  • 0 to $4

so it become a variable. little did I know, the $2 generated into a string data type. what I want is it generated into an array of string data type, because I found out that Golang can translate an array of string from ["1", "2", "3"] into '1', '2', '3' , just like what I want to input inside postgres IN parenthesis.

in Golang side, I made a custom struct like this

type SearchMajorReq struct {
    Name           string   `json:"name"`
    FieldOfStudies []string `json:"field_of_studies"`
    Limit          int32    `json:"limit"`
    Page           int32    `json:"page"`
}

in hope that this is the correct data type to send a JSON req body like this

{
    "name":"",
    "field_of_studies": ["1", "2", "3"],
    "limit": 10,
    "page": 1
}

but it doesn't works. I have an error in FieldOfStudies part. How can I solve this?

Adhitya Resadikara
  • 99
  • 8
  • 2
    `IN` does not expect an array, it expects a list of values. If you give it an array it will treat it as a single value and attempt to match the column against that array, rather than individual elements in that array. If you want to pass an array then you should use `ANY` instead of `IN` and wrap the Go slice with `pq.Array`. Just like it is shown in `sqlc`'s [documentation](https://docs.sqlc.dev/en/stable/howto/select.html#passing-a-slice-as-a-parameter-to-a-query) (down at the bottom the `listAuthors` query). – mkopriva Sep 01 '22 at 10:46
  • Thank you very much @mkopriva. That's the answer. But then I found another problem. How if I want to send an empty array but the result is to show all of the data? I replace LIKE ANY to ILIKE ANY but the results are the same – Adhitya Resadikara Sep 02 '22 at 06:08
  • sorry, but somehow I figured it out myself. So, thank you so so so so so much @mkopriva – Adhitya Resadikara Sep 02 '22 at 06:31

0 Answers0