0

I have one field & it has comma separated ID, so i want to find from that selected id, here is my code,

.get(function(req, res) {
  knex.select('*')
  .from('exam')
  .whereRaw('? = any(regexp_split_to_array(student_id))', [req.params.id])
  .then(function(rows) {
    //return res.send(rows);
    console.log(rows);
  })
  .catch(function(error) {
    console.log(error)
  });
});

===> while i am using KNEX it will give an Error Like this,

{ error: function regexp_split_to_array(text) does not exist
  name: 'error',
  length: 220,
  severity: 'ERROR',
  code: '42883',
  detail: undefined,
  hint: 'No function matches the given name and argument types. You might need to add explicit type casts.',
  position: '37',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_func.c',
  line: '523',
  routine: 'ParseFuncOrColumn' 
}
  1. in student_id column i have ID like this, 33,34,35,36
  2. in req.params.id i got only one single ID like, 35.
  3. so i want that rows which have included 35 ID, in Same Table.

enter image description here

===> So i want Only Two Rows (2,3) because it has Included ID = 35.

Apurv Chaudhary
  • 1,672
  • 3
  • 30
  • 55
  • Knex only creates SQL query and send it to SQL. The SQL on its own cannot search in "comma separated" strings natively. You can search for student_id LIKE '%35%', but it will be expensive operation (if you have lot of rows) and it will also find ID with 35 included like 135. Maybe you can use some special SQL commands that can do something with commas. – libik Mar 29 '18 at 13:18
  • @libik yes, with the use of LIKE if any other column have 354 ID then it will fetch that ROW also, but i need only rows which have 35 ID. – Apurv Chaudhary Mar 29 '18 at 13:21
  • So maybe something like this? https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns – libik Mar 29 '18 at 13:23
  • If you're storing multiple ids in the same column you're using the wrong relation type. You should probably be using `belongsToMany` instead. – devius Mar 29 '18 at 14:17
  • @devius it's not about Relation, i want data from SAME table, not from reference table. – Apurv Chaudhary Mar 30 '18 at 05:11
  • @devius i want only that rows which have included 'student_id' = 35 from SAME table. – Apurv Chaudhary Mar 30 '18 at 05:24
  • It is about relation because you want to get the exams that belong to a student with a certain id. That could easily be achieved with a `belongsToMany` relation where an Exam [belongsToMany](http://bookshelfjs.org/#Model-instance-belongsToMany) Student, which would create a join table between the two models. – devius Mar 30 '18 at 10:08

1 Answers1

2

Assuming you are using PostgreSQL database (I saw you use phpPgAdmin on the screenshot). You can use regexp_split_to_array function to convert your string to array (obviously :). And the perform search over the resulting array using any.

In SQL words, it can be written like this

select '35' = any(regexp_split_to_array('33,34,35,36', E','));

In your query, you can replace .where with

.whereRaw("? = any(regexp_split_to_array(student_id, E','))", [req.params.id])

But keep in mind, this can be performance-heavy request, as for each row you execute string split operation. A better way of doing this (assuming it is necessary for your project to contain array values in one row) is to store your student_id in the Array type and add gin index on student_id column and perform search operations like this

select * from table where student_id @> '{35}';
coockoo
  • 2,294
  • 17
  • 26
  • i get this ERROR 'Exam.whereRaw is not a function', actually i am using BookshelfJS, so how can i use this Query in BookshelfJS? – Apurv Chaudhary Mar 30 '18 at 10:21
  • @Chaudhary ok, try this one `.where(bookshelf.knex.raw('? = any(regexp_split_to_array(student_id))', [req.params.id]))` I'm actually not familliar with bookshelfjs, but using `knex` tools it should be working. – coockoo Mar 30 '18 at 12:08
  • i update my question, while i am using KNEX it will give an Error. – Apurv Chaudhary Mar 30 '18 at 13:00
  • I'm sorry, I missed second parameter for `regexp_split_to_array` in `knex` representation, `, E','`. I'll update my response – coockoo Mar 30 '18 at 13:04
  • coockoo ===> thanxxxx now it's working fine, thanxx. – Apurv Chaudhary Mar 30 '18 at 13:06